SQL Server: Why are dates in ISO-8601 language dependent?

Question

I need help handling the date format in SQL Server.

If you try the following, it will return the correct result:

SET LANGUAGE English SELECT CAST('2013-08-15' AS DATETIME) -- 2013-08-15 00:00:00.000 

This, however, will result in a conversion error, because, apparently, SQL Server interprets “8” as day and “15” as month:

 SET LANGUAGE German SELECT CAST('2013-08-15' AS DATETIME) -- Conversion failed when converting date and/or time from character string. 

I know that I can use a language-independent (slightly adapted ISO-8601) format YYYYMMDD (without a dash), and it will work in any language.

I don’t understand why YYYY-MM-DD is language dependent when it is clearly stated in SQL Books

"The interpretation depends on a combination of the string literal format, ... and the default language parameter settings ... Some of these string literals do not affect these parameters ... The ISO 8601 format is independent of these settings and is an international standard.

http://technet.microsoft.com/en-us/library/ms180878%28v=sql.105%29.aspx

Even if you look at the date format returned by select * from sys.syslanguages , it is not indicated - the date format is dmy , so it does not correspond to the ISO-8601 format.

So the questions are:

• Why is the ISO-8601 format dependent on the language, even if Books Online says otherwise?
• Where can I find the exact format used by SQL Server when parsing ISO-8601 dates?

UPDATE:

Further reading http://technet.microsoft.com/en-us/library/ms180878%28v=sql.105%29.aspx#ISO8601Format , he says: "To use the ISO 8601 format, you must specify each element in the format. This includes T, colons (:), + or -, and periods (.) '(For example, 2004-05-23T14: 25: 10).

The table above ( http://technet.microsoft.com/en-us/library/ms180878%28v=sql.105%29.aspx#StringLiteralDateandTimeFormats ) states that ISO 8601 Numeric is not dependent on DATEFORMAT, but is also not multilingual. I am not sure where to find additional information about the multilingual part, although, for example, the exact format used in each language.

Answer 1

This related question may help with ISO-8601 date languages ​​and formats. Why is SQL Server misinterpreting this date in ISO 8601 format?

See the Final datetime datatypes manual article, which was also linked in the answer for more information on the datetime types used by SQL Server.

Answer 2

My guess would be to maintain backward compatibility. The new data types in SQL Server 2008 datetime2 and date are independent of SET LANGUAGE or SET DATEFORMAT . Here is the connect element that suggests changing the behavior for datetime .

Answer 3

It is hard to answer a question starting with "Why" :-)

This may not answer your question, but for dates there is one string format that will work in all locales: "YYYYMMDD"

Try:

 SET LANGUAGE English SELECT CAST('20130815' AS DATETIME) SET LANGUAGE German SELECT CAST('20130815' AS DATETIME) SET LANGUAGE Japanese SELECT CAST('20130815' AS DATETIME) 

This will give the expected result.