How can it be getting UTC dates wrong?

Question

How can it be getting UTC dates wrong?

Paul-962113

SSC Journeyman

Points: 83
More actions
December 9, 2008 at 5:59 am

#196894

I've just installed my SQL2005/ASP.NET2 based application on a new server. It works fine on other machines, but now its started getting my dates backwards.
Here's the bit that's making my head explode - I'm already using UTC formats!
INSERT INTO table1(id, datefield) VALUES(1, '2008-12-09 11:00:00')
when submitted through ado.net winds up as the 12th Sept instead of the 9th Dec (which is what I wanted).
But if I type exactly the same line into sql server management studio on the server it works properly.
How is this even possible? Any help will be appreciated, I'm going completely mad.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

The date’s format in SQL Server is determined according to the user’s default language, so if 2 different users execute this line:

INSERT INTO table1(id, datefield) VALUES(1, '2008-12-09 11:00:00')

They could have different results in Table1 (depending on the users’ default language). Beside user’s default language there are other session level set options that could modify the way that the date will be interpreted. In order to make sure that the date will always be interpreted the same way regardless of the user’s or session’s configuration, you should use a format that will always be interpreted the same way. There are few formats like that (you can look them up in BOL at the explanation about cast and convert functions). One of them is a string with the fallowing structure – ‘yyyymmdd hh:mm:ss.nnn’. Fallowing your example, if the SQL statement would look this way:

INSERT INTO table1(id, datefield) VALUES(1, '20081209 11:00:00')

It will always insert the same date.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul-962113 SSC Journeyman Points: 83 More actions · Answer 2

So "yyyy-mm-dd hh:mm:ss" isnt universally recognised, but "yyyymmdd hh:mm:ss.nnn" is?

Weird

Also, when you say refer to the users language, I take it you mean the SQL Server user? I've looked at the windows users regional settings and not found anything.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Paul (12/9/2008)
So "yyyy-mm-dd hh:mm:ss" isnt universally recognised, but "yyyymmdd hh:mm:ss.nnn" is?
Weird
Also, when you say refer to the users language, I take it you mean the SQL Server user? I've looked at the windows users regional settings and not found anything.

I was reffering to the SQL Server users.

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Dashes throw things off, only slamming everything together requires everyone to assume the same thing. I know, I know, it's annoying, but the rest of the world is different from the US.

The Windows settings, default server language, users' default language, all can affect date interpretation. Likely ODBC/OLEDB settings on the client come into play as well with dates. Be specific if you can to avoid issues.