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

Question

我有如下一段内嵌SQL语句，这段语句是我从C# Windows服务运行的：

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代码，则可以成功运行。

有什么想法是什么原因导致这个问题？

- amateur

---

将来的读者请注意：如果输入的日期早于允许的最早日期（1753年1月1日），也会出现此错误。 - A Jar of Clay

Answer 1

模糊的日期格式将根据登录语言进行解释。这是有效的。

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

- Martin Smith

---

是的，这很可能是个原因。解决方法是使用明确的日期格式，例如年-月-日：'2011-03-28 18:03:40'。 - Joe White

@Niall - 如果这是你的格式字符串，那么你也将分钟作为月份传递或反之亦然。 - Martin Smith

@Richard - 他们两个都是03。这只是巧合吗？难道不需要是“yyyyMMdd HH:mm:ss”吗？我完全同意关于参数的观点。想看看是否有任何有用的内置字符串格式。 - Martin Smith

@Martin - 感谢您提供的用户登录建议，正是这个问题。 - amateur

yyyymmdd hh:mm:ss 应该变成 yyyyMMdd hh:mm:ss，否则 yyymmdd 会被解释为 年/分钟/日。 - DGibbs

Answer 2

但如果我将这个 SQL 语句放到 SQL 管理工具中运行，它会毫无问题地运行。

如果您有权限的话，将服务帐户更改为您自己的登录帐户，这样就可以继承您的语言/区域偏好设置。

问题的关键是：

我使用以下代码进行转换 -> date.Value.ToString("MM/dd/yyyy HH:mm:ss")

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

- RichardTheKiwi

---

这是用户账户的问题！！！应用程序运行的账户默认语言设置为英国英语，但当我自己登录 SQL Server Management Studio 时，我的账户默认为英语！ - amateur

@Niall - Martin也知道这一点，正如他在回答中所预先说明的那样。我只是指出了如何使用现有代码使服务工作。谢谢。 - RichardTheKiwi

+1 参数化查询使这变得更加简单！ - Martin Smith

我认为HH:MM应该改成HH:mm，而不是在时间部分使用月份数字！ - Toby Speight

Answer 3

我认为在C#和SQL之间处理日期的最佳方法是使用参数化查询，并始终在C#上使用DateTime对象和其提供的ToString()格式选项。

在处理SQL Server上的日期之前，最好先执行set datetime <format>（您可以在MSDN上找到设置日期格式的说明），以免遇到问题，例如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()的解释）

- Iván Sainz

Answer 4

这是一个日期格式问题。在爱尔兰，3月28日的标准日期格式为“28-03-2011”，而“03/28/2011”是美国（以及许多其他国家）的标准格式。

- srgerg

Answer 5

我知道这个解决方案与OP的情况有些不同，但是你可能像我一样从谷歌搜索这个问题的标题而被重定向到了这里，也许你正在面临我曾经遇到的同样的问题。
有时候你会因为日期时间无效而出现此错误，即你的日期（以字符串形式）指向超过该月份天数的某一天！ 例如：CONVERT(Datetime, '2015-06-31')导致了我的这个错误，当时我正在将一个来自MySql的语句转换为SQL Server（MySql没有抱怨！这使得错误更难以捕捉）。

- Mohsen Kamrani

Answer 6

你可以使用下面的函数来初始化你的DateTime变量： DATETIMEFROMPARTS (年, 月, 日, 小时, 分钟, 秒, 毫秒)

- Serhii Lisovyi

---

你的回答可以通过提供更多支持信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人可以确认你的答案是正确的。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Community

Answer 7

我遇到了一个问题，当我使用SQL时，它与MYSQL不同。解决方案是按照以下格式输入： =date('m-d-y h:m:s'); 而不是 =date('y-m-d h:m:s');

- Evans Cherono

Answer 8

JAVA8：使用LocalDateTime.now().toString()。

- ariabele