Specify character literal for date and time type in SQL Server

Question

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. What does it actually mean?

Answer 1

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