SQL Server CASE表达式报错排查：日期差规则计算值的语句问题

原代码的语法问题 #

• 括号不匹配：ELSE分支内的CASE语句存在多余的闭合括号，例如WHEN DATEDIFF (m, exstart_start, datecoms)) - 12 <= 0)中，DATEDIFF后多了一个)，条件末尾也多了一个)
• 外层CASE结构混乱：原代码在主CASE的ELSE分支后额外添加了一个独立的ELSE，且存在多余的END)，导致整个表达式结构不完整

需求匹配的修正代码 #

根据你的计算逻辑，重新整理并修正语法后的代码如下：

CASE 
    WHEN DATEDIFF(m, exstart_start, dateord) <= 0 THEN
        CASE
            WHEN DATEDIFF(m, exstart_start, datecoms) - 24 >= 0 THEN DATEDIFF(m, exstart_start, datecoms) - 24
            WHEN DATEDIFF(m, exstart_start, datecoms) - 12 >= 0 THEN DATEDIFF(m, exstart_start, datecoms) - 12
            ELSE 0
        END
    ELSE
        DATEDIFF(m, exstart_start, dateord)
END AS diffexstarttoordered

优化建议（可选） #

为避免重复调用DATEDIFF函数影响性能，可以先通过CTE或子查询计算出所需的月份差值：

WITH CTE_Values AS (
    SELECT 
        exstart_start,
        dateord,
        datecoms,
        DATEDIFF(m, exstart_start, dateord) AS diff_ord,
        DATEDIFF(m, exstart_start, datecoms) AS diff_coms
    FROM 你的表名
)
SELECT
    CASE 
        WHEN diff_ord <= 0 THEN
            CASE
                WHEN diff_coms - 24 >= 0 THEN diff_coms - 24
                WHEN diff_coms - 12 >= 0 THEN diff_coms - 12
                ELSE 0
            END
        ELSE
            diff_ord
    END AS diffexstarttoordered
FROM CTE_Values

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