使用TRY_CAST/TRY_CONVERT仍报日期转换错误的Demo日期滚动脚本问题

一、先揪出转换错误的根源 #

你用了TRY_CAST/TRY_CONVERT还报错，大概率是这两个原因：

• 字段里藏着非日期格式的脏数据：比如乱码、纯数字、不符合日期规则的字符串，TRY_*函数会把这些转成NULL，但后续如果用NULL做日期差值计算，还是会触发错误。先把这些脏数据找出来：

  SELECT @ColumnName, *
  FROM @TableName
  WHERE TRY_CAST(@ColumnName AS DATETIME) IS NULL
  

• 变量@DateMax的类型不对：如果它是字符串类型，直接参与日期计算会触发隐式转换，必须先转成日期类型再用：

  DECLARE @DateMax_DT DATETIME = TRY_CONVERT(DATETIME, @DateMax)
  IF @DateMax_DT IS NULL
  BEGIN
      RAISERROR('@DateMax无法转换为有效日期', 16, 1)
      RETURN
  END
  

二、修复更新逻辑（解决逐行更新不生效的问题） #

逐行更新没效果，基本是游标读取的是快照数据（更新后的数据没同步到游标），或者WHERE条件匹配错了。直接放弃游标，用基于集合的更新，效率高还稳定：

核心更新代码 #

DECLARE @CurrentDate DATETIME = GETDATE()
DECLARE @TwoYearsAgo DATETIME = DATEADD(YEAR, -2, @CurrentDate)
-- 先拿到表中真正的最大有效日期
DECLARE @DateMax_DT DATETIME = (SELECT MAX(TRY_CAST(@ColumnName AS DATETIME)) FROM @TableName)

-- 第一步：处理脏数据，把无法转成日期的字段设为近两年内的随机值
UPDATE @TableName
SET @ColumnName = CONVERT(VARCHAR(20), DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 730, @TwoYearsAgo), 120)
WHERE TRY_CAST(@ColumnName AS DATETIME) IS NULL

-- 第二步：按规则偏移正常日期
UPDATE @TableName
SET @ColumnName = CONVERT(VARCHAR(20), 
    CASE
        -- 原日期和最大日期差超过2年的，直接拉到近两年内
        WHEN DATEDIFF(DAY, TRY_CAST(@ColumnName AS DATETIME), @DateMax_DT) > 730 THEN
            DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 730, @TwoYearsAgo)
        -- 其余日期按和原最大日期的差值，偏移到以当前日期为基准
        ELSE
            DATEADD(DAY, DATEDIFF(DAY, TRY_CAST(@ColumnName AS DATETIME), @DateMax_DT), @CurrentDate)
    END, 120)
WHERE TRY_CAST(@ColumnName AS DATETIME) IS NOT NULL

补充：如果字段本身是DATETIME类型 #

不用转成字符串，直接用日期类型计算，彻底避免转换错误：

UPDATE @TableName
SET @ColumnName = 
    CASE
        WHEN DATEDIFF(DAY, @ColumnName, @DateMax_DT) > 730 THEN
            DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 730, @TwoYearsAgo)
        ELSE
            DATEADD(DAY, DATEDIFF(DAY, @ColumnName, @DateMax_DT), @CurrentDate)
    END
WHERE @ColumnName IS NOT NULL

三、验证更新结果 #

执行完后，一定要做这几个验证：

• 检查最大日期是否等于当前日期：

  SELECT MAX(TRY_CAST(@ColumnName AS DATETIME)) FROM @TableName
  

• 确认所有日期都在近两年内：

  SELECT * FROM @TableName
  WHERE TRY_CAST(@ColumnName AS DATETIME) < @TwoYearsAgo
  

• 脏数据是否全部修复：

  SELECT * FROM @TableName WHERE TRY_CAST(@ColumnName AS DATETIME) IS NULL
  

四、如果非要用游标（不推荐） #

必须用游标时，改用DYNAMIC类型游标，确保读取的是实时数据，同时每次更新前先验证日期有效性：

DECLARE date_cursor CURSOR DYNAMIC FOR
SELECT ID, TRY_CAST(@ColumnName AS DATETIME) AS CleanDate
FROM @TableName

OPEN date_cursor
FETCH NEXT FROM date_cursor INTO @ID, @CleanDate

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @CleanDate IS NOT NULL
    BEGIN
        UPDATE @TableName
        SET @ColumnName = CONVERT(VARCHAR(20), 
            CASE
                WHEN DATEDIFF(DAY, @CleanDate, @DateMax_DT) > 730 THEN
                    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 730, @TwoYearsAgo)
                ELSE
                    DATEADD(DAY, DATEDIFF(DAY, @CleanDate, @DateMax_DT), @CurrentDate)
            END, 120)
        WHERE ID = @ID
    END
    FETCH NEXT FROM date_cursor INTO @ID, @CleanDate
END

CLOSE date_cursor
DEALLOCATE date_cursor

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