Error converting string to datetime due to locale

Question

I'm having difficulties with the locale in a particular instance of SQL Server 2008 R2 Express.

I am in the UK and the following fails:

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

Error message:

Converting 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 is English. Sort "if it matters" - Latin1_General_CI_AS.

The "language" of the database is English (USA), but then it is the same as another instance on another server, and the above SQL will not work.

Any thoughts?

Answer 1

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

This parameter is specific to the user of the SQL connection issuing the query.

One way to check if this is a problem ... Run this in Management Studio and log in 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 for the SQL user accordingly.

Answer 2

Do not use YYYY-MM-DD for date literals; always use YYYYMMDD . It never fails, regardless of language, date settings, language settings, regional settings, etc.:

 SELECT CAST('20121231' AS DATETIME); 

Perhaps worth a read: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

Answer 3

You must explicitly define the date format for your conversion, in this case it is 120:

 SELECT CONVERT(DATETIME,'2012-12-31',120) 

You can look at this page to see more date formats: http://msdn.microsoft.com/en-us/library/ms187928.aspx