SQL中使用日期字符串变量执行UPDATE语句的问题

问题核心原因 #

你遇到的转换失败，大概率是日期字符串变量里藏着换行符、制表符或者多余空白，导致STRING_SPLIT拆分后出现无效的字符串，转datetime时出错。直接写字符串能成功是因为手动输入时已经去掉了这些隐藏字符。

可行解决方案 #

方案1：彻底清理字符串后再转换

先把变量里的换行、回车符去掉，再清理每个日期的前后空格，最后用TRY_CONVERT过滤无效值，避免整个语句报错。

示例代码：

-- 定义带换行的日期变量（用户维护这个即可）
DECLARE @HolidayDates NVARCHAR(MAX) = '
    2024-01-01
    2024-02-10
    2024-04-04
    2024-05-01
';

-- 清理字符串并转换为合法日期集合
WITH ValidHolidays AS (
    SELECT 
        TRY_CONVERT(DATE, LTRIM(RTRIM(value))) AS HolidayDate
    FROM STRING_SPLIT(
        -- 先去掉回车和换行，换成逗号分隔
        REPLACE(REPLACE(@HolidayDates, CHAR(13), ''), CHAR(10), ','), 
        ','
    )
    -- 过滤空字符串（拆分后可能产生的空项）
    WHERE LTRIM(RTRIM(value)) <> ''
)
-- 更新公假日标识
UPDATE dbo.days
SET PublicHoliday = 1
-- 只匹配转换成功的合法日期
WHERE CONVERT(DATE, DateColumn) IN (SELECT HolidayDate FROM ValidHolidays WHERE HolidayDate IS NOT NULL);

方案2：用表值参数替代字符串变量（更适合长期维护）

如果用户每1-2年要更新一次，用表值参数比字符串变量更稳妥，完全避免字符串转换的坑，维护起来也直观。

步骤1：创建自定义表类型

CREATE TYPE DateListType AS TABLE (HolidayDate DATE);
GO

步骤2：使用表值参数更新

DECLARE @Holidays DateListType;
-- 用户直接维护这里的日期列表即可
INSERT INTO @Holidays (HolidayDate)
VALUES 
    ('2024-01-01'),
    ('2024-02-10'),
    ('2024-04-04'),
    ('2024-05-01');

UPDATE dbo.days
SET PublicHoliday = 1
WHERE CONVERT(DATE, DateColumn) IN (SELECT HolidayDate FROM @Holidays);

排查问题小技巧 #

如果想知道到底是哪个日期出问题，可以先执行以下语句查看拆分和转换结果：

DECLARE @HolidayDates NVARCHAR(MAX) = '你的日期字符串';

SELECT 
    value AS 原始字符串,
    LTRIM(RTRIM(value)) AS 清理后字符串,
    TRY_CONVERT(DATE, LTRIM(RTRIM(value))) AS 转换结果
FROM STRING_SPLIT(REPLACE(REPLACE(@HolidayDates, CHAR(13), ''), CHAR(10), ','), ',')
WHERE LTRIM(RTRIM(value)) <> '';

转换结果为NULL的就是导致报错的无效日期，直接修正即可。

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