使用CASE表达式与DATEFROMPARTS时触发无效日期参数错误

问题出在SQL Server的表达式求值逻辑：CASE表达式虽然逻辑上是短路判断，但SQL引擎会提前计算所有分支的表达式，哪怕对应的WHEN条件不成立。也就是说，哪怕你的样本数据都不满足两个WHEN条件，DATEFROMPARTS的参数还是会被计算，一旦这些参数无法构成有效日期（比如月份是13、年份格式错误等），就会抛出你看到的错误。

这里提供三种可行的解决办法：

1. 使用TRY_DATEFROMPARTS替代DATEFROMPARTS #

SQL Server 2012及以上版本支持TRY_DATEFROMPARTS函数，它和DATEFROMPARTS功能一致，但当参数无效时会返回NULL而非抛出错误。直接替换即可：

CASE
    WHEN OM.OfferName LIKE '%-_I%'
         AND TRY_CAST(LEFT(OM.OfferName, 4) AS int) IS NOT NULL
        THEN TRY_DATEFROMPARTS(RIGHT(LEFT(OM.OfferName, 4), 2) + 2000, LEFT(OM.OfferName, 2) , 1) 
    WHEN TRY_CAST(RIGHT(LTRIM(RTRIM(OM.OfferName)), 4) AS INT) IS NOT NULL
       THEN TRY_DATEFROMPARTS(RIGHT(OM.OfferName, 2)+ 2000, LEFT(RIGHT(OM.OfferName, 4), 2) , 1) 
    ELSE NULL
END

2. 嵌套CASE确保参数仅在有效时计算 #

通过嵌套CASE，给DATEFROMPARTS的每个参数加上判断，只有当参数能转为有效整数时才传入数值，否则传入NULL（DATEFROMPARTS收到NULL时会返回NULL，不会报错）：

CASE
    WHEN OM.OfferName LIKE '%-_I%'
         AND TRY_CAST(LEFT(OM.OfferName, 4) AS int) IS NOT NULL
        THEN DATEFROMPARTS(
            CASE WHEN TRY_CAST(RIGHT(LEFT(OM.OfferName, 4), 2) AS INT) IS NOT NULL THEN RIGHT(LEFT(OM.OfferName, 4), 2) + 2000 ELSE NULL END,
            CASE WHEN TRY_CAST(LEFT(OM.OfferName, 2) AS INT) IS NOT NULL THEN LEFT(OM.OfferName, 2) ELSE NULL END,
            1
        )
    WHEN TRY_CAST(RIGHT(LTRIM(RTRIM(OM.OfferName)), 4) AS INT) IS NOT NULL
       THEN DATEFROMPARTS(
            CASE WHEN TRY_CAST(RIGHT(OM.OfferName, 2) AS INT) IS NOT NULL THEN RIGHT(OM.OfferName, 2) + 2000 ELSE NULL END,
            CASE WHEN TRY_CAST(LEFT(RIGHT(OM.OfferName, 4), 2) AS INT) IS NOT NULL THEN LEFT(RIGHT(OM.OfferName, 4), 2) ELSE NULL END,
            1
        )
    ELSE NULL
END

3. 提前解析参数到CTE/子查询 #

先把需要的年月参数用TRY_CAST转换好，确保只有有效数值才保留，无效则为NULL，之后再在CASE中构造日期：

WITH OfferParsed AS (
    SELECT 
        OM.OfferName,
        -- 解析第一个分支的年月
        TRY_CAST(RIGHT(LEFT(OM.OfferName, 4), 2) AS INT) + 2000 AS ValidYear1,
        TRY_CAST(LEFT(OM.OfferName, 2) AS INT) AS ValidMonth1,
        -- 解析第二个分支的年月
        TRY_CAST(RIGHT(OM.OfferName, 2) AS INT) + 2000 AS ValidYear2,
        TRY_CAST(LEFT(RIGHT(OM.OfferName, 4), 2) AS INT) AS ValidMonth2
    FROM YourTableName OM -- 替换为你的实际表名
)
SELECT
    CASE
        WHEN OfferName LIKE '%-_I%' AND ValidYear1 IS NOT NULL AND ValidMonth1 IS NOT NULL
            THEN DATEFROMPARTS(ValidYear1, ValidMonth1, 1)
        WHEN TRY_CAST(RIGHT(LTRIM(RTRIM(OfferName)), 4) AS INT) IS NOT NULL AND ValidYear2 IS NOT NULL AND ValidMonth2 IS NOT NULL
            THEN DATEFROMPARTS(ValidYear2, ValidMonth2, 1)
        ELSE NULL
    END AS OfferDate
FROM OfferParsed

这三种方法都能避免DATEFROMPARTS收到无效参数，其中第一种最简单，只要你的SQL Server版本支持TRY_DATEFROMPARTS就优先用它。

内容的提问来源于stack exchange，提问作者Chad Portman