Accepted date format changed overnight

Question

Since yesterday I started encountering errors related to date formats in SQL Server 2008.

Up until yesterday the following used to work.

EXEC MyStoredProc '2010-03-15 00:00:00.000'

Since yesterday I started getting out of range errors. After investigating I discovered the date as above is now being interpreted as "the 3rd of the 15th month" which will cause a out of range error.

I have been able to fix this using the following format with the "T".

EXEC MyStoredProc '2010-03-15T00:00:00.000'

By using this format its working fine. Basically all I'm trying to find out is if there is some Hotfix or patch that could have caused this, as all my queries using the first mentioned formats have been working for months.

Also this is not a setting that was changed by someone in the company as this is occurring on all SQL 2005/2008 servers

Answer 1

It sounds like you've got a mixture of date styles - yyyy/MM/dd vs yyyy/dd/MM - which seem to be reverse UK and US style dates.

If all servers are showing the same behaviour it could be that just the machine executing the code has changed rather than the other machines/SQL servers in the network.

Double check that the date format or indeed culture settings are what you expect them to be on that machine. Assuming they weren't you might be able to find out what changes to the machine were made over the weekend from the event logs or Windows Update history.

Answer 2

To avoid these kind of problems you should always specify your date format. For your purposes you should use:

SET DATEFORMAT ymd;

See this MSDN page - note the comment that

ydm is not supported for date, datetime2 and datetimeoffset data types

so you might have to use a different format if you're using one of those data types.

Answer 3

The language setting of the session can change this.

--This works
set language english
select cast('2010-03-15 00:00:00.000' as datetime)

--This doesn't
set language french
select cast('2010-03-15 00:00:00.000' as datetime)

With the T in between, it always works. If you want a space, then leave out the hyphens.

--This works
set language english
select cast('2010-03-15T00:00:00.000' as datetime)

--This works
set language french
select cast('2010-03-15T00:00:00.000' as datetime)

--This works
set language english
select cast('20100315 00:00:00.000' as datetime)

--This works
set language french
select cast('20100315 00:00:00.000' as datetime)

So my guess is that your app has changed... or some setting on your client computer.

Answer 4

Collation can effect dates, as well as the language.

To check what default language a server has installed, use the following SQL command:

   sp_configure 'default language'

If the resulting value is 0, the default language U.S. English. If the result is not 0, run the following SQL command to find the installed default language setting and date format used:

  select name ,alias, dateformat
   from syslanguages
      where langid =
      (select value from master..sysconfigures
         where comment = 'default language')

NOW as to the system updates updates, those were in 2007 to adjust to the DST changes mandated back then. Machine specific: you CAN set the clock on a specific machine to NOT adjust for DST - check that setting per machine (Windows it is on Control Panel/Date and Time under XP for instance).

IF you choose to use the dashes, you may have issues in dates. If you take out the dashes, SQL Server will never misinterpret the data:

EXEC MyStoredProc '2010-03-15 00:00:00.000'  

this is likely getting a time with a negative hour offset, which is in this case invalid.(just a guess) vs

EXEC MyStoredProc '20100315 00:00:00.000' 

Note that the T in there is the ISO8601 format, and thus the dashes are allowed.

Answer 5

Ok I just read a post over at Simons SQL Blog (http://cli.gs/hqaR4) about this specific issue. So one solution to this problem is to Use the new DATETIME2 datatype as it understands languages that doesn't use MDY dates