10

I need some help understanding date format handling in SQL Server.

If you try the following, it will return a 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 the day and '15' as the 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 the language-independent (slightly adapted ISO-8601) format YYYYMMDD (without dashes), and it will work in any language.

I don't understand however why YYYY-MM-DD is language dependent, when SQL Books clearly says

"The interpretation depends on the combination of string literal format, ... and default language option settings. ... Some string literal formats are not affected by these settings. ... The ISO 8601 format does not depend on these settings and is an international standard."

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

Even looking at the dateformat returned by select * from sys.syslanguages gives no indication - the date format is dmy, so it doesn't match the ISO-8601 format either.

So, the questions are:

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

UPDATE:

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

The table right above (http://technet.microsoft.com/en-us/library/ms180878%28v=sql.105%29.aspx#StringLiteralDateandTimeFormats) says that the ISO 8601 Numeric is not DATEFORMAT dependent, but it also is not Multilanguage. I'm not sure where to find additional information about the Multilanguage part though - e.g., the exact format used in each language.

matk
  • 1,528
  • 2
  • 14
  • 25
  • the german format is this: `SET LANGUAGE German; SELECT CAST('15.08.2013' AS DATETIME)`. what about just using convert? – Brett Schneider Jan 23 '14 at 08:33
  • @Brett Schneider: I'm aware that it is; however I'm asking about the handling of ISO-8601 format dates. – matk Jan 23 '14 at 08:34
  • "the exact format used in each language" is in the `dateformat` column in `sys.syslanguages`. – Mikael Eriksson Jan 23 '14 at 08:59
  • @Mikael Eriksson: The value in the dateformat column is dmy for German, but the ISO format YYYY-MM-DD. Something doesn't match. – matk Jan 23 '14 at 09:01
  • Yes, and that is because days comes before months in `dmy` and in `YYYY-MM-DD` months comes before days. SQL Server is smart enough to find a four digit year even if it does not match the position in `dmy` but when finding months and days it uses the order of `d` and `m` in `SET DATEFORMAT` to figure out what is month and what is day. – Mikael Eriksson Jan 23 '14 at 09:06
  • @Mikael Eriksson: It seems this is what's going on, I tested this with several different languages. Are you aware of any official documentation of this behavior? – matk Jan 23 '14 at 09:39
  • Nothing other than [Numeric Date Formats](http://technet.microsoft.com/en-us/library/ms180878(v=sql.105).aspx#NumericDateFormats) on the page you already found. It says that "12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting" but **"A four-digit year will be interpreted as year."** – Mikael Eriksson Jan 23 '14 at 10:33

3 Answers3

3

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

See the article The ultimate guide to the datetime datatypes which was also linked in the answer for more information on the datetime types used by SQL Server.

Community
  • 1
  • 1
Ilessa
  • 602
  • 8
  • 27
1

My guess would be to maintain backwards compatibility. The new datatypes in SQL Server 2008 datetime2 and date is not dependent on SET LANGUAGE or SET DATEFORMAT. Here is a connect item that suggests to change the behaviour for datetime as well.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
1

Hard to answer a question starting with "Why" :-)

This may not answer your question, but for dates there is one string format which will work across 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.

mwelser
  • 11
  • 1
  • 2