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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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) &...
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top