amateur amateur - 3 months ago 55
SQL Question

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

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

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