SQL Server Date Casting Issue

Question

On my install of SQL Server if I perform the following

SELECT CAST('2008-05-03 00:00:00' AS DATETIME), CAST('2008-05-03T00:00:00' AS DATETIME)

Then I get the following result

2008-03-05 00:00:00.000 2008-05-03 00:00:00.000

Now this is odd in itself as I'm not sure why it's parsing the first date as yyyy/dd/mm (my login is set to british english btw) and that date format is not a standard one AFAIK.

I'm not sure where to start poking to try to resolve this odd parse issue which doesn't seem to occur ion any of my colleagues systems.

Answer 1

Your first format is indeed a language-dependent format (ODBC canonical), and since your system is defined to be "British English", it will be interpreted as YYYY-DD-MM (if it's "American English", it would be interpreted as YYYY-MM-DD).

However, the second one is the ISO-8601 standard date format (Wikipedia) that will work on any installation of SQL Server, regardless of your language or locale settings. It's format is always YYYY-MM-DDTHH:MM:SS and will be interpreted as such.

Try this:

SET LANGUAGE british

SELECT 
    CAST('2008-05-03 00:00:00' AS DATETIME) 'UK Date',
    CAST('2008-05-03T00:00:00' AS DATETIME) 'UK ISO Date'

SET LANGUAGE  us_english

SELECT 
    CAST('2008-05-03 00:00:00' AS DATETIME) 'US Date',
    CAST('2008-05-03T00:00:00' AS DATETIME) 'US ISO Date'

My output is:

UK Date                    UK ISO Date
2008-03-05 00:00:00.000    2008-05-03 00:00:00.000

US Date                    US ISO Date
2008-05-03 00:00:00.000    2008-05-03 00:00:00.000

Answer 2

Can you use convert instead? This will allow you to specify the date format which I'm not sure you can do using cast:

SELECT CONVERT(DATETIME, '2008-05-03 00:00:00', 120), CONVERT(DATETIME, '2008-05-03T00:00:00', 126)

You can take a look at the various formats here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 3

It is using the collate configuration as in your session. You can avoid ambiguity setting the date format in line:

SET dateformat dmy
SELECT CAST('2008-05-03 00:00:00' AS DATETIME), CAST('2008-05-03T00:00:00' AS DATETIME)
-- 2008-03-05 00:00:00.000 2008-05-03 00:00:00.000

SET dateformat mdy
SELECT CAST('2008-05-03 00:00:00' AS DATETIME), CAST('2008-05-03T00:00:00' AS DATETIME)
-- 2008-05-03 00:00:00.000 2008-05-03 00:00:00.000