将 varchar 数据类型转换为 datetime 数据类型导致值超出范围
我有以下从 C# Windows 服务运行的内联 SQL：
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 数据库运行此代码时，出现以下错误：
System.Data.SqlClient.SqlException：将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。 该声明已终止。
但是，如果我从 SQL Management Studio 中获取该 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'
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
不明确的日期格式将根据登录语言进行解释。 问题
。
如果您使用具有正确数据类型的参数化查询，则可以避免这些 您还可以使用明确“未分隔”格式
yyyyMMdd hh:mm:ssAmbiguous date formats are interpreted according to the language of the login. This works
This doesn't
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如果您可以随意，请将服务帐户更改为您自己的登录名，这将继承您的语言/区域首选项。
问题的真正症结在于：
请开始使用参数化查询，以便将来不会遇到这些问题。它也更加稳健、可预测和最佳实践。
If you are at liberty to, change the service account to your own login, which would inherit your language/regional perferences.
The real crux of the issue is:
Please start using parameterized queries so that you won't encounter these issues in the future. It is also more robust, predictable and best practice.
我认为在 C# 和 SQL 之间处理日期的最佳方法当然是使用参数化查询，并且始终使用 C# 上的 DateTime 对象及其提供的 ToString() 格式化选项。
您最好执行
set datetime（这里有设置日期格式说明在 MSDN 上），然后再在 SQL Server 上处理日期，这样您就不会遇到麻烦，例如set datetime ymd。每个连接只需执行一次，因为它在打开时保留格式，因此一个好的做法是在打开数据库连接后立即执行此操作。然后，您始终可以使用“yyyy-MM-dd HH:mm:ss:ffff”格式。
要将 DateTime 对象传递给参数化查询，您可以使用
DateTime.ToString('yyyy-MM-dd HH:mm:ss:ffff')。要在 C# 上解析奇怪格式的日期，您可以使用
DateTime.ParseExact()方法，您可以选择准确指定输入格式：DateTime.ParseExact(;, 'dd/MM-yyyy',CultureInfo.InvariantCulture) 。 这里有 MSDN 上的 DateTime.ParseExact() 解释 ）I think the best way to work with dates between C# and SQL is, of course, use parametrized queries, and always work with DateTime objects on C# and the ToString() formating options it provides.
You better execute
set datetime <format>(here you have the set dateformat explanation on MSDN) before working with dates on SQL Server so you don't get in trouble, like for exampleset datetime ymd. You only need to do it once per connection because it mantains the format while open, so a good practice would be to do it just after openning the connection to the database.Then, you can always work with 'yyyy-MM-dd HH:mm:ss:ffff' formats.
To pass the DateTime object to your parametrized query you can use
DateTime.ToString('yyyy-MM-dd HH:mm:ss:ffff').For parsing weird formatted dates on C# you can use
DateTime.ParseExact()method, where you have the option to specify exactly what the input format is:DateTime.ParseExact(<some date string>, 'dd/MM-yyyy',CultureInfo.InvariantCulture). Here you have the DateTime.ParseExact() explanation on MSDN)这是日期格式问题。在爱尔兰，3 月 28 日的标准日期格式为“28-03-2011”，而“03/28/2011”是美国（以及其他许多国家）的标准。
It's a date format issue. In Ireland the standard date format for the 28th of March would be "28-03-2011", whereas "03/28/2011" is the standard for the USA (among many others).
我知道这个解决方案与OP的情况有点不同，但是正如我所做的那样，您可能已经从谷歌搜索这个问题的标题被重定向到这里，也许您面临着与我相同的问题。
有时您会收到此错误，因为您的日期时间无效，即您的日期（字符串格式）指向的日期超过了该月的天数！
例如： CONVERT(Datetime, '2015-06-31') 在我从 MySql 转换语句时导致了这个错误（这没有争论！并且使错误更难捕获）到 SQL Server。
I know that this solution is a little different from the OP's case, but as you may have been redirected here from searching on google the title of this question, as I did, maybe you're facing the same problem I had.
Sometimes you get this error because your date time is not valid, i.e. your date (in string format) points to a day which exceeds the number of days of that month!
e.g.:
CONVERT(Datetime, '2015-06-31')caused me this error, while I was converting a statement from MySql (which didn't argue! and makes the error really harder to catch) to SQL Server.您可以使用下一个函数来初始化 DateTime 变量：
DATETIMEFROMPARTS（年、月、日、小时、分钟、秒、毫秒）
You could use next function to initialize your DateTime variable:
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
JAVA8：使用 LocalDateTime.now().toString()
JAVA8: Use LocalDateTime.now().toString()
我在使用 SQL 时遇到了这个问题，它与 MYSQL 不同
解决方案采用以下格式：
=日期('mdy h:m:s');
而不是
=日期('ymd h:m:s');
i faced this issue where i was using SQL it is different from MYSQL
the solution was puting in this format:
=date('m-d-y h:m:s');
rather than
=date('y-m-d h:m:s');