I have a date format problem - please help 1

[Decus]

The Problem:
When I insert dates in SQL Server 2000 into a field having a data type 'datetime', the date is recorded in US format even though I had specified a european format, e.g., 01/09/2002 gets recorded in the database as 09/01/2002.

The Code:

<cfset thedate = &quot;01/09/2003&quot;>
<cfquery name=&quot;insertdate&quot; datasource=&quot;main&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO testdate (expirydate)
VALUES ('#DateFormat(CreateODBCDate(thedate),&quot;dd/mm/yyyy&quot#')
</cfquery>

SQL Server is configured to use UK settings and the computer's regional settings is set to the UK and the date format is set to dd/mm/yyyy.

I noticed that if I don't use #DateFormat(CreateODBCDate(thedate),&quot;dd/mm/yyyy&quot# in the sql command and just put the date in directly, i.e.,
INSERT INTO testdate (expirydate)
VALUES ('01/09/2003') then the date gets recorded OK.

Why is this happening??

 

[Sarky78]

We had a problem with this as well but this was only with ASP. Have you checked that the IUSR account has got the UK date settings as well? This is the account that is used by the system for internet operations such as CF.

If this doesn't help what version of SQL Server are u using ?

 

[r937]

actually, dates in sql/server are not stored in any date format at all -- they are stored as integers

start by making sure your &quot;thedate&quot; field is not mis-interpreted

<cfset thedate=CreateDate(2003,09,01)>

in your testdate table, add a column called &quot;method&quot; so you can keep track of the formats and what they end up with

insert into testdate
(method, expirydate)
values
('raw 2003-09-01 string', '2003-09-01')

insert into testdate
(method, expirydate)
values
('raw thedate', #thedate#)

insert into testdate
(method, expirydate)
values
('raw thedate string', '#thedate#')

insert into testdate
(method, expirydate)
values
('createODBCdate(thedate)', createODBCdate(thedate))

and so on

they may not all work the way you expect, try as many combinations as you can think of

now, print these all out, using CONVERT to make sure you are actually seeing the dates correctly

select method, CONVERT(char(10),expirydate,103)
from testdate

style 103 specifies you want to see dd/mm/yyyy

remember, internally, sql/server stores dates an integers, so by using CONVERT you are making sure you are seeing them the way you want, and not some default format

see CAST and CONVERT

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

for other style formats

rudy

http://rudy.ca

 

[Decus]

Thanks arperry and r937 for your input. Using createODBCdate(thedate) inside the sql INSERT command (without single quotes around it) seems to have solved the problem for storing the date on the SQL Server, and CONVERT(char(10),expirydate,103) has helped to convert the date to dd/mm/yyyy format, but now the problem's with the final date calcs:

<CFSET daysdiff = DateDiff(&quot;d&quot;,DateFormat(Now(),&quot;dd/mm/yyyy&quot,DateFormat(getkeyinfo.expirydate,&quot;dd/mm/yyyy&quot)>

If expirydate is the result from the CONVERT(char(10),expirydate,103) query which produces a dd/mm/yyyy result and if todays date is 06/09/2002 (dd/mm/yyyy) then daysdiff will equal 449 which is obviously wrong.

However, if I remove the DateFormat command for the Now() date, i.e.,
<CFSET daysdiff = DateDiff(&quot;d&quot;,Now(),DateFormat(getkeyinfo.expirydate,&quot;dd/mm/yyyy&quot)> then I get the correct answer, daysdiff = 359.

And <CFOUTPUT>#expirydate#</CFOUTPUT> shows as 01/09/2003 in the browser, but
<CFOUTPUT>#DateFormat(expirydate,&quot;dd/mm/yyyy&quot#></CFOUTPUT> shows as 09/01/2003

What's going on here? I'm extremely confused now.

arperry, I'm using SQL Server 7 (incorrectly mentioned as SQL Server 2000 in previous post) and I can't find out how to set check/set the UK date settings for the IUSR account. Could anyone help me out on this?