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

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 Management Studio中运行SQL片段，它将毫无问题地运行。

Any ideas what may be causing this issue?

你知道是什么引起了这个问题吗?

6 个解决方案

#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

如果您使用带有正确数据类型的参数化查询，就可以避免这些问题。您还可以使用明确的“unseparated”格式yyyyMMdd hh:mm:ss

#2

But if i take the piece of sql and run it from sql management studio, it will run without issue.

但是如果我从sql管理studio中运行sql并运行它，它将毫无问题地运行。

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")

我使用下面的代码来转换->日期。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.

请开始使用参数化查询，以便将来不会遇到这些问题。它也更加健壮、可预测和最佳实践。

#3

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 (这里有MSDN上的set dateformat说明)，这样就不会遇到麻烦，例如set datetime ymd。每个连接只需要执行一次，因为它在打开时属于格式，所以一个很好的实践是在打开到数据库的连接之后执行。然后，你可以一直使用“yyyy-MM-dd:mm:ss:ffff”格式。要将DateTime对象传递给参数化查询，可以使用DateTime。ToString(“yyyy-MM-dd HH:mm:ss:飞行符”)。要在c#上解析奇怪的格式化日期，可以使用DateTime. parseexact()方法，其中可以选择确切地指定输入格式:DateTime。ParseExact( <一些日期字符串> ,dd / MM-yyyy,CultureInfo.InvariantCulture)。这里有MSDN上的DateTime.ParseExact()解释)

#4

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”则是美国的标准(包括其他许多国家)。

#5

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.

我知道这个解决方案与OP的情况有点不同，但是当你在谷歌上搜索这个问题的标题时，你可能会被重定向到这里，就像我所做的，你可能会遇到和我一样的问题。有时您会得到这个错误，因为您的日期时间是无效的，例如您的日期(字符串格式)指向一个超过该月天数的日期!例如:CONVERT(Datetime， '2015-06-31')导致我犯这个错误，而我正在从MySql转换语句(这并没有争论!)并且使错误更难捕获)到SQL Server。

#6

JAVA8: Use LocalDateTime.now().toString()

.toString JAVA8:使用LocalDateTime.now()()

#1