Error converting string to datetime due to locale
date, datetime, sql-server
Solution
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
Problem
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?