sql server-sql:将varchar转换为datetime时出错

Question

WITH Valid_dates (ValidDate)
AS
(
    SELECT '19' + SUBSTRING(column, 0, 3) + '-' + SUBSTRING(column, 3, 2) + '-' + SUBSTRING(column, 5, 2) AS ValidDate
    FROM table
    WHERE SUBSTRING(column, 3, 2) <= '12' --Max month
    AND SUBSTRING(column, 3, 2) >= '01' --Min month
    AND ISNULL(SUBSTRING(column, 3, 2), '') <> '' --Empty string
    AND SUBSTRING(column, 5, 2) <= '31' --Max day
    AND SUBSTRING(column, 5, 2) >= '01' --Min month
    AND ISNULL(SUBSTRING(column, 5, 2), '') <> '' --Empty string
    AND LEN('19' + SUBSTRING(column, 0, 3) + '-' + SUBSTRING(column, 3, 2) + '-' + SUBSTRING(column, 5, 2)) = 10 --Must match 10 character format
)
SELECT CONVERT(DATETIME, ValidDate)
FROM Valid_dates

当我在SQLServerManagementStudio中执行此查询时，我会收到以下错误消息："将varchar数据类型转换为datetime数据类型导致值超出范围。">

我已过滤掉所有无效日期。如你所见，2月30日、4月31日、6月31日等不存在的日期本身没有被过滤掉，但我已经检查过了，它们不存在。仅运行CTE查询会产生大量结果，我找不到任何差异。

Answer 1

这是SQL Server的一个问题。它不保证查询中子句的求值顺序。where(即使在CTE中(在查询的其余部分之前"求值"是毫无意义的。

注意：这是优化器的特性。通过重新安排查询的不同组件的评估，优化器可以创建更好的查询计划。

在SQL Server 2012+中，最简单的方法是TRY_CONVERT():

with . . .
select try_convert(datetime, validdate)
from valid_dates;

在早期版本中，您可以使用case:

select (case when <all your conditions here>
             then convert(datetime, validdate)
        end)

SQL Server确实保证在正常情况下(聚合存在一些奇怪的情况(对case语句进行顺序求值，因此这也解决了问题。

Answer 2

我确信这与文化有关。

你没有指定实际的输入，从你的代码中，我想这是一些未分离的格式，没有920130这样的年份，也就是1992年1月30日？

这将在类似1992-01-30的东西中转换。

您的默认区域性可能是使用类似yyyy-dd-mm的东西，这将导致"月"高于12的超出范围的错误…

尝试使用102作为区域性密钥：

SELECT CONVERT(DATETIME, ValidDate,102)
FROM Valid_dates

更新

如果我的假设是真的，那么尝试要容易得多

SELECT TRY_CAST('19' + column AS DATE) FROM table

SQL Server允许以本机方式强制转换看起来像yyyymmdd的未分离字符串。如果无法进行强制转换，则使用TRY_CAST将返回NULL。

对于旧版本(<2012(，请使用此

SELECT CASE WHEN ISDATE('19' + column)=1 THEN CAST('19' + column AS DATE) ELSE NULL END FROM table