SQL 将 varchar 数据类型转换为 datetime 数据类型导致值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5466100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
conversion of a varchar data type to a datetime data type resulted in an out-of-range value
提问by amateur
I have the following piece of inline SQL that I run from a C# windows service:
我有以下一段从 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'
When I run this against a SQL Server database from within the application, I get the following error:
当我从应用程序中针对 SQL Server 数据库运行此程序时，出现以下错误：
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.
System.Data.SqlClient.SqlException：将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。该语句已终止。
But if I take the piece of SQL and run it from SQL Management Studio, it will run without issue.
但是，如果我使用 SQL 片段并从 SQL Management Studio 运行它，它将毫无问题地运行。
Any ideas what may be causing this issue?
任何想法可能导致此问题？
回答by Martin Smith
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
如果您使用具有正确数据类型的参数化查询，则可以避免这些问题。您还可以使用明确的“未分隔”格式yyyyMMdd hh:mm:ss
回答by RichardTheKiwi
But if i take the piece of sql and run it from sql management studio, it will run without issue.
但是，如果我使用 sql 并从 sql management studio 运行它，它将毫无问题地运行。
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:
问题的真正症结在于：
I use the following to convert -> date.Value.ToString("MM/dd/yyyy HH:mm:ss")
我使用以下转换 -> date.Value.ToString("MM/dd/yyyy HH:mm:ss")
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.
请开始使用参数化查询，以免将来遇到这些问题。它也是更强大、更可预测和最佳实践。
回答by Iván Sainz
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 example set 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)
我认为在 C# 和 SQL 之间处理日期的最佳方法当然是使用参数化查询，并且始终使用 C# 上的 DateTime 对象及其提供的 ToString() 格式化选项。在 SQL Server 上处理日期之前，
您最好先执行set datetime <format>（这里有 MSDN 上的 set dateformat 解释），这样您就不会遇到麻烦，例如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(<some date string>, 'dd/MM-yyyy',CultureInfo.InvariantCulture). 在这里你有 MSDN 上的 DateTime.ParseExact() 解释）
回答by mok
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 daysof 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.
我知道这个解决方案与 OP 的情况略有不同，但是由于您可能已经从在 google 上搜索此问题的标题而被重定向到这里，就像我所做的那样，也许您正面临着与我相同的问题。
有时您会收到此错误，因为您的日期时间无效，即您的日期（以字符串格式）指向超过该月天数的一天！例如：CONVERT(Datetime, '2015-06-31')导致我出现此错误，而我正在将语句从 MySql（没有争论！并且使错误更难以捕捉）转换为 SQL Server。
回答by srgerg
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).
是日期格式问题。在爱尔兰，3 月 28 日的标准日期格式是“28-03-2011”，而“03/28/2011”是美国（以及许多其他日期）的标准。
回答by ariabele
JAVA8: Use LocalDateTime.now().toString()
JAVA8：使用 LocalDateTime.now().toString()
回答by Evans Cherono
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');
我在使用 SQL 时遇到了这个问题，它与 MYSQL 不同，解决方案采用以下格式：=date('mdy h:m:s'); 而不是 =date('ymd h:m:s');
