Error converting string to datetime due to locale

Question

I'm having a lot of difficulty with locale's in a particular instance of SQL Server 2008 R2 Express.

I'm in the UK and the following fails:

SELECT CAST('2012-12-31' AS DATETIME)

Error message:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The Windows server locale is British English. My login locale is British English. Collation 'if that matters' is Latin1_General_CI_AS.

The database 'language' is English (United States) but then this is the same as another instance on a different server and the above SQL doesnt fail.

Any thoughts?

Answer 1

For the user making the database connection -- the SQL user -- set the language to English.

This is a setting specific to the SQL user of the connection issuing the query

One way to check if this is a problem... Run this in Management Studio and login as the SQL user who issues the query

SET LANGUAGE English
SELECT CAST('2012-12-31' AS DATETIME)

If this works, set the default language of the SQL user appropriately

Answer 2

Don't use YYYY-MM-DD for date literals, always use YYYYMMDD. This will never fail, regardless of locale, dateformat settings, language settings, regional settings, etc:

SELECT CAST('20121231' AS DATETIME);

A worthwhile read perhaps:

• Bad habits to kick : mis-handling date / range queries