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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
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
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,351
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,171,584
Messages
5,876,307
Members
433,193
Latest member
BruxoTrader

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