Tony Stark Tony Stark - 1 month ago 7
SQL Question

specify character literal for date and time type in sql server

In the book, Ben-Gan, Itzik. T-SQL Fundamentals , there is the following statement.


formats ‘YYYY-MM-DD’ and ‘YYYY-MM-DD hh:mm...’ are language dependent
when converted to DATETIME or SMALLDATETIME and language neutral when
converted to DATE, DATETIME2, and DATETIMEOFFSET.


I understand that when specifying date and time literal, sql server can interpret the same literal differently according to the language setting.

However, I was unable to clearly understand the above statement. So could someone help me clarify what is actually means? Thanks in advance for any help!

Answer

From this answer: The new datatypes in SQL Server 2008 datetime2 and date are not dependent on SET LANGUAGE or SET DATEFORMAT.

Some european locales have 'YYYY-DD-MM', but datetime2 and date always use 'YYYY-MM-DD'. See connect article.

E.g:

Set dateformat dmy;
Select cast ('2012-02-01' as datetime) [DateTime], cast ('2012-02-01' as datetime2) [DateTime2],
cast ('2012-02-01' as date) [Date];

Results in:

DateTime                DateTime2                   Date
2012-01-02 00:00:00.000 2012-02-01 00:00:00.0000000 2012-02-01