将 varchar 数据类型转换为 datetime 数据类型导致值超出范围

2021-12-27 00:00:00 service sql sql-server sqlexception

我有以下一段从 C# windows 服务运行的内联 SQL:

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'

当我从应用程序中针对 SQL Server 数据库运行此程序时，出现以下错误:

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

System.Data.SqlClient.SqlException:将 varchar 数据类型转换为 datetime 数据类型导致值超出范围.声明已终止.

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.

但是，如果我使用 SQL 的一部分并从 SQL Management Studio 运行它，它将毫无问题地运行.

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?

推荐答案

不明确的日期格式根据登录语言进行解释.这有效

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)

这没有

set dateformat dmy

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

如果您使用具有正确数据类型的参数化查询，则可以避免这些问题.您还可以使用明确未分隔"格式yyyyMMdd hh:mm:ss

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

相关文章