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