SQL Server：varchar数据类型转换错误

Question

我正在尝试运行一条SQL查询，但是出现了错误。

将varchar数据类型转换为datetime数据类型时，结果超出范围。

代码：

SELECT 
    ClientId,
    DatePassed,
    Lender
FROM 
    dbo.tbl_Profile_Mortgage
WHERE 
    DatePassed = '2011-04-28 00:00:00.000'

我已将数据类型转换为以下方式

SELECT 
    ClientId,
    Convert (Date, DatePassed)
    Lender
FROM 
    dbo.tbl_Profile_Mortgage
WHERE 
    DatePassed = '2011-04-28 00:00:00.000'

然而，这仍然给我同样的错误。

列DatePassed的类型为datetime null

如果有建议，将不胜感激。

- RustyHamster

---

可能是毫秒数出了问题。此外，在查询中使用 date = 不是很有效。通常你会想要做一个范围，比如从午夜到午夜之间。 - crthompson

嗨，Paqogomez，我已经尝试了 WHERE DatePassed Between '2011-04-28 00:00:00.000' and '2011-04-28 23:59:59.000'，但我仍然得到错误。 - RustyHamster

毫秒呢？ - João Pereira

当你只使用 Between '2011-04-28 and '2011-04-29' 时会发生什么？默认情况下，这将给你一个24小时的范围。你可能还会遇到 yyyy-mm-dd 的问题。尝试改用 mm-dd-yyyy。 - crthompson

当我使用between语句时，我收到了与之前相同的错误。我已经尝试了dd-mm-yyyy，它似乎可以工作，但是没有返回任何数据。 - RustyHamster

在深入研究转换内容之前（下面答案中的Bertrand链接是一个很好的参考），请确保您要转换的列中的数据是可靠的。如果该列中存在尾随/前导字符或无效数据（非数字字符），则可能会引发此错误。 - Techie Joe

Answer 1

从概念上讲，您可能认为 where 子句在 convert() 之前执行。但实际查询运行的顺序并不一定是这样的。唯一保证执行顺序的 SQL 语句是 case（但也不总是如此）。

您可以尝试以下操作：

SELECT ClientId,
       (case when isdate(DatePassed) = 1 then Convert (Date, DatePassed) end),
       Lender
FROM dbo.tbl_Profile_Mortgage
WHERE DatePassed = '2011-04-28 00:00:00.000';

编辑：

鉴于DatePassed已经是日期时间格式，那么你的错误非常不寻常。这不是该列转换的问题，因为没有涉及到varchar。

我能想到的唯一可能是常量。也许你有一个奇怪的默认日期设置，它试图获取第28个月的第4天。如果是这样，你可以通过使用格式显式转换来轻松解决此问题：

SELECT ClientId,
       Convert(Date, DatePassed),
       Lender
FROM dbo.tbl_Profile_Mortgage
WHERE DatePassed = convert(date, '2011-04-28 00:00:00.000', 121);

Aaron Bertrand在他的博客中对这个问题进行了广泛讨论。顺便说一句，尽管我认识到他提到的YYYY-MM-DD格式存在的问题，但我仍然默认使用它。我认为微软选择不识别这种ISO标准格式是一个错误（但只是因为我认为它是一个错误，并不能说服其他人）。

- Gordon Linoff

---

此外，务必避免隐式转换。虽然这可能无法解决您的问题，但将 DatePassed = '2011-04-28 00:00:00.000' 更改为 DatePassed = CAST('2011-04-28 00:00:00.000' AS DATETIME) 是一个好习惯。 - Steve Pettifer

嘿，Gordon，这仍然给我一个错误：“将varchar数据类型转换为datetime数据类型导致超出范围的值。” - RustyHamster

Answer 2

SQL Server支持许多格式-请参见CAST和CONVERT的MSDN Books Online。大多数这些格式都取决于您的设置-因此，这些设置有时可能有效-有时则无效。我猜在您的情况下，您正在使用一种语言设置，它根本无法与您使用的字符串文字配合使用。

解决此问题的方法是使用SQL Server支持的（稍加改动的）ISO-8601日期格式 - 该格式始终工作-无论您的SQL Server语言和日期格式设置如何。 ISO-8601格式由SQL Server支持，有两种类型：

• YYYYMMDD仅适用于日期（没有时间部分）；请注意： 没有破折号！，这非常重要！ YYYY-MM-DD不独立于SQL Server中的日期格式设置，并且在所有情况下都不起作用！

或者：

• YYYY-MM-DDTHH:MM:SS 是日期和时间的格式 - 注意：这个格式中有短横线（但可以省略），并且在 DATETIME 的日期和时间部分之间有一个固定的 T 作为分隔符。

这适用于 SQL Server 2000 及更新版本。

如果您使用 SQL Server 2008 或更新版本，并使用 DATE 数据类型（仅限 DATE - 不是 DATETIME ！），那么您确实也可以使用 YYYY-MM-DD 格式，而且不管您 SQL Server 中的任何设置都可以正常工作。

别问我为什么这个主题如此棘手和有些令人困惑 - 就是这样。但是使用 YYYYMMDD 格式，您应该可以在任何版本的 SQL Server 和 SQL Server 中的任何语言和日期格式设置中正常工作。

SQL Server 2008及更高版本的建议是，如果只需要日期部分，则使用DATE，如果需要日期和时间，则使用DATETIME2(n)。如果可能的话，应尽量开始逐步淘汰DATETIME数据类型。

在你的具体情况中 - 在WHERE子句中使用此字符串：

WHERE DatePassed = '2011-04-28T00:00:00.000';

请注意日期和时间部分之间的字面上的 T。

- marc_s