Date Conversion (UK format vs. US format)

rpremaza

New Member
Joined
Apr 4, 2006
Messages
15
Hello All.
I'm (attempting) to put together a macro will reference a "start date" cell and an "end date" cell. The macro will contain SQL that will hit a sybase database and extract the needed info. Here is my problem. All the dates in the sybase DB are stored in UK format (yyyy-mm-dd). I've formated the 2 cells to this format, the macro is still seeing them in the windows fromat (mm/dd/yyyy). Now I know if I go into control panel and change the format to the UK format my macro works.
QUESTION: Is there something I can do in the macro so that I can get the UK date format and the US format date to work correctly, some type of conversion code would be great.

If it helps, here is the SQL code that I'm using:
select distinct n.statdate, a.lastname+','+a.firstname, q.primaryagent, q.sharepct, p.policyid, c.lastname+','+c.firstname, p.plantype, l.basicface, p.annprem, p.targetprem, p.fyc, p.premmode, p.ex1035prem, p.stat, a.gano, n.Status from dba.policy p, dba.nbapphistory n, dba.polagent q, dba.life l, dba.contact a, dba.contact c, dba.ltc m where (n.StatDate>=? And N.StatDate<=?)and (n.status = '93' or n.status = 18) and n.policyno = p.policyno and n.policyno = q.policyno and n.policyno *= l.policyno and q.agentno = a.clientno and p.clientno = c.clientno order by a.gano,p.policyid, p.stat

(the section : "(n.StatDate>=? And N.StatDate<=?)" is generating a prompt for the date. This is sort of working, but the requirements that I have specify that the dates should be in cells, and the macro pulls from those cells)

Thanks to all
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What does your code look like with UK dates?

Maybe try eg:

n.StatDate>=" & Format(A1,"yyyy-mm-dd") & "And N.StatDate<=" & Format(A2,"yyyy-mm-dd")
 

rpremaza

New Member
Joined
Apr 4, 2006
Messages
15
Andrew, thanks for your quick response.

I was not able to come up with anthing to fix the conflict (US vs. UK)

I put in your suggested code, but i was getting compile errors. I had to change it to the following to get past the compile errors:

(n.StatDate>= " & Format(A1, "yyyy-mm-dd") & " and N.StatDate<=" & Format(A2, "yyyy-mm-dd") & ")

Now I'm getting "run-time error '1004' SQL syntax error.
 

rpremaza

New Member
Joined
Apr 4, 2006
Messages
15

ADVERTISEMENT

Andrew,
Here is the complete macro:

Sub Test()
'
' Macro1 Macro
' Macro recorded 10/3/2006 by Tests
'
Dim mSQL As String
ActiveSheet.Name = "run"
mSQL = "select distinct n.statdate, a.lastname+','+a.firstname, q.primaryagent, q.sharepct, p.policyid, c.lastname+','+c.firstname, p.plantype, l.basicface, p.annprem, p.targetprem, p.fyc, p.premmode, p.ex1035prem, p.stat, a.gano, n.Status from dba.policy p, dba.nbapphistory n, dba.polagent q, dba.life l, dba.contact a, dba.contact c, dba.ltc m where (n.StatDate>= " & Format(A1, "yyyy-mm-dd") & " and N.StatDate<=" & Format(A2, "yyyy-mm-dd") & ")and (n.status = '93' or n.status = 18) and n.policyno = p.policyno and n.policyno = q.policyno and n.policyno *= l.policyno and q.agentno = a.clientno and p.clientno = c.clientno order by a.gano,p.policyid, p.stat"

With Sheets("run").QueryTables.Add(Connection:="ODBC;DSN=mydb", Destination:=Range("A9"))
.CommandText = mSQL
.Refresh (True)

End With

End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, I was a bit slapdash there:

Code:
mSQL = "select distinct n.statdate, a.lastname+','+a.firstname, q.primaryagent, q.sharepct, p.policyid, c.lastname+','+c.firstname, p.plantype, l.basicface, p.annprem, p.targetprem, p.fyc, p.premmode, p.ex1035prem, p.stat, a.gano, n.Status from dba.policy p, dba.nbapphistory n, dba.polagent q, dba.life l, dba.contact a, dba.contact c, dba.ltc m where (n.StatDate>= " & Format(Range("A1"), "yyyy-mm-dd") & " and N.StatDate<=" & Format(Range("A2"), "yyyy-mm-dd") & ")and (n.status = '93' or n.status = 18) and n.policyno = p.policyno and n.policyno = q.policyno and n.policyno *= l.policyno and q.agentno = a.clientno and p.clientno = c.clientno order by a.gano,p.policyid, p.stat"
 

rpremaza

New Member
Joined
Apr 4, 2006
Messages
15

ADVERTISEMENT

Again, thanks for the quick response. I think we are getting closer, but i just ran into a different problem

After instering the code above, I'm getting "General ODBC error"

Not sure if this will help, but I ran the code in MS Query. What i get there is
Syntax error or access violation: near 'a1' in ...=" & format(range("(a1)"),"yyyy-mm-dd) &...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
rpremaza

I think what you need to do is find out how Sysbase deals with dates.

What does the SQL of a query using hard-coded dates look like when you record a macro?
 

rpremaza

New Member
Joined
Apr 4, 2006
Messages
15
If I use the following "hard coded" dates I get the the desired results:
select distinct n.statdate, a.lastname+','+a.firstname, q.primaryagent, q.sharepct, p.policyid, c.lastname+','+c.firstname, p.plantype, l.basicface, p.annprem, p.targetprem, p.fyc, p.premmode, p.ex1035prem, p.stat, a.gano, n.Status from dba.policy p, dba.nbapphistory n, dba.polagent q, dba.life l, dba.contact a, dba.contact c, dba.ltc m where (n.StatDate>= '2006-09-01' and N.StatDate<='2006-10-01')and (n.status = '93' or n.status = 18) and n.policyno = p.policyno and n.policyno = q.policyno and n.policyno *= l.policyno and q.agentno = a.clientno and p.clientno = c.clientno order by a.gano,p.policyid, p.stat
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Seems to have some single quotes. Try:

Code:
mSQL = "select distinct n.statdate, a.lastname+','+a.firstname, q.primaryagent, q.sharepct, p.policyid, c.lastname+','+c.firstname, p.plantype, l.basicface, p.annprem, p.targetprem, p.fyc, p.premmode, p.ex1035prem, p.stat, a.gano, n.Status from dba.policy p, dba.nbapphistory n, dba.polagent q, dba.life l, dba.contact a, dba.contact c, dba.ltc m where (n.StatDate>='" & Format(Range("A1"), "yyyy-mm-dd") & "' and N.StatDate<='" & Format(Range("A2"), "yyyy-mm-dd") & "')and (n.status = '93' or n.status = 18) and n.policyno = p.policyno and n.policyno = q.policyno and n.policyno *= l.policyno and q.agentno = a.clientno and p.clientno = c.clientno order by a.gano,p.policyid, p.stat"
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top