sql-server - 在 SQL Server 中将字符串转换为日期时间时出现异常

Question

在 with 语句中:

WITH queryResult AS
(
    SELECT 
        CONVERT(DATETIME, '2017-12-12 ' + PARTIALDATE) 
    FROM 
        tableA 
    WHERE 
        ISDATE('2017-12-12 ' + PARTIALDATE) = 1
)
....

在这个 block 中，我会得到异常:

Conversion failed when converting date and/or time from character string.

所以我做了一些改变:

WITH subQueryResult AS
(
    SELECT 
        PARTIALDATE 
    FROM 
        tableA 
    WHERE 
        ISDATE('2017-12-12 ' + PARTIALDATE) = 1
),
queryResult AS
(
    SELECT 
        CONVERT(DATETIME, '2017-12-12 ' + PARTIALDATE) 
    FROM 
        subQueryResult
)

而且有效!

但为什么呢？

Answer 1

WHERE 不会在转换发生之前进行过滤。 case 语句保证操作顺序。以下应该有效:

WITH queryResult AS
(
    SELECT 
        CASE WHEN ISDATE('2017-12-12 ' + PARTIALDATE) = 1 THEN CONVERT(DATETIME, '2017-12-12 ' + PARTIALDATE) ELSE NULL END
    FROM 
        tableA 
    WHERE 
        ISDATE('2017-12-12 ' + PARTIALDATE) = 1
)