使用表值函数更新Date维度表时遇操作数类型冲突问题

1. 排查函数调用的参数错误 #

最常见的触发该错误的原因是向函数的INT类型参数@Year传入了DATE类型的值。比如你的UPDATE语句如果是这样：

UPDATE d
SET HolidayName = eh.HolidayName
FROM Date d
JOIN dbo.GetEasterHolidays(d.Date) eh ON d.Date = eh.HolidayDate

这里d.Date是DATE类型，而函数要求@Year是INT，直接传入就会报类型不兼容。修正方法是用YEAR()函数提取年份的INT值：

UPDATE d
SET HolidayName = eh.HolidayName
FROM Date d
JOIN dbo.GetEasterHolidays(YEAR(d.Date)) eh ON d.Date = eh.HolidayDate

2. 确认函数返回的字段类型 #

如果上述修正无效，需要先确认表值函数返回的日期字段类型（INFORMATION_SCHEMA无法查询，用系统视图获取）：

SELECT 
    name AS 返回字段名,
    system_type_name AS 字段数据类型
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM dbo.GetEasterHolidays(2024)', 
    NULL, 
    0
);

如果查询发现HolidayDate是INT类型（比如存储为yyyymmdd格式的数字），而你的Date表日期字段是DATE类型，需要在关联时做类型转换：

UPDATE d
SET HolidayName = eh.HolidayName
FROM Date d
JOIN dbo.GetEasterHolidays(YEAR(d.Date)) eh 
    ON d.Date = CAST(CAST(eh.HolidayDate AS VARCHAR(8)) AS DATE)

3. 修正函数返回的日期类型（推荐） #

更彻底的方法是修改函数，让它直接返回DATE类型的日期字段，避免后续转换。比如用高斯复活节算法计算日期并确保结果为DATE类型：

ALTER FUNCTION dbo.GetEasterHolidays(@Year INT)
RETURNS TABLE
AS
RETURN (
    WITH EasterCalc AS (
        SELECT 
            a = @Year % 19,
            b = @Year / 100,
            c = @Year % 100,
            d = b / 4,
            e = b % 4,
            f = (b + 8) / 25,
            g = (b - f + 1) / 3,
            h = (19 * a + b - d - g + 15) % 30,
            i = c / 4,
            k = c % 4,
            l = (32 + 2 * e + 2 * i - h - k) % 7,
            m = (a + 11 * h + 22 * l) / 451,
            month = (h + l - 7 * m + 114) / 31,
            day = ((h + l - 7 * m + 114) % 31) + 1
    )
    SELECT 
        CAST(CONCAT(@Year, '-', month, '-', day) AS DATE) AS HolidayDate,
        'Easter Sunday' AS HolidayName
    UNION ALL
    SELECT 
        DATEADD(DAY, -2, CAST(CONCAT(@Year, '-', month, '-', day) AS DATE)) AS HolidayDate,
        'Good Friday' AS HolidayName
    FROM EasterCalc
)

修改后，再用步骤1中的UPDATE语句（传入YEAR(d.Date)作为参数）即可正常关联更新。

4. 验证更新结果 #

执行完UPDATE后，用以下语句验证复活节相关日期的HolidayName是否正确：

SELECT Date, HolidayName
FROM Date
WHERE HolidayName IN ('Good Friday', 'Easter Sunday')
ORDER BY Date;

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