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

Question

I have the following piece of inline SQL that I run from a C# windows service:

UPDATE table_name SET      status_cd = '2',      sdate = CAST('03/28/2011 18:03:40' AS DATETIME),      bat_id = '33acff9b-e2b4-410e-baaf-417656e3c255',      cnt = 1,      attempt_date = CAST('03/28/2011 18:03:40' AS DATETIME)  WHERE id = '1855' 

When I run this against a SQL Server database from within the application, I get the following error:

System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

But if I take the piece of SQL and run it from SQL Management Studio, it will run without issue.

Any ideas what may be causing this issue?

Answer 1

Ambiguous date formats are interpreted according to the language of the login. This works

set dateformat mdy  select CAST('03/28/2011 18:03:40' AS DATETIME) 

This doesn't

set dateformat dmy  select CAST('03/28/2011 18:03:40' AS DATETIME) 

If you use parameterised queries with the correct datatype you avoid these issues. You can also use the unambiguous "unseparated" format yyyyMMdd hh:mm:ss