SQL Server动态日期列转Decimal计算更新列值异常排查

首先，咱们先揪出你遇到的问题根源：你错误地把「生成列名字符串的表达式」直接拿来做减法运算了，而不是引用对应的数据库列。

举个例子，当你写 LEFT(CONVERT(varchar, DATEADD(month, -1, GETDATE()), 112),6) - LEFT(CONVERT(varchar, DATEADD(month, -2, GETDATE()), 112),6) 时，SQL 并不会把这两个函数的结果当成列名（比如[202006]和[202005]），而是把它们当成字符串'202006'和'202005'来做减法——字符串相减时SQL会隐式转成整数，202006 - 202005 = 1，这就是为什么所有行的X列都变成1的原因！

另外，你的列是varchar类型，直接相减还会有隐式转换的风险（比如遇到非数字字符就会报错），必须显式转成数值类型（比如decimal）后再计算。

正确的实现方式：动态SQL #

因为你需要每月自动适配当前月份的列名，必须用动态SQL来拼接UPDATE语句，步骤如下：

1. 定义变量存储动态列名 #

先计算出需要用到的目标列名：

• 要更新X列：用「当前月份-1」的列减去「当前月份-2」的列（比如当前是202007，就是[202006]-[202005]）
• 要更新Y列：用「当前月份-2」的列减去「当前月份-3」的列（比如[202005]-[202004]）

2. 拼接并执行动态UPDATE语句 #

这里用TRY_CONVERT来处理可能的转换错误（如果列里有非数字内容，会返回NULL，避免整个语句报错）：

DECLARE @colX1 VARCHAR(6), @colX2 VARCHAR(6), @colY1 VARCHAR(6), @colY2 VARCHAR(6)
DECLARE @sql NVARCHAR(MAX)

-- 计算需要引用的列名
SET @colX1 = LEFT(CONVERT(VARCHAR, DATEADD(MONTH, -1, GETDATE()), 112), 6) -- 上月，如202006
SET @colX2 = LEFT(CONVERT(VARCHAR, DATEADD(MONTH, -2, GETDATE()), 112), 6) -- 上上月，如202005
SET @colY1 = LEFT(CONVERT(VARCHAR, DATEADD(MONTH, -2, GETDATE()), 112), 6) -- 上上月，如202005
SET @colY2 = LEFT(CONVERT(VARCHAR, DATEADD(MONTH, -3, GETDATE()), 112), 6) -- 上上月的上月，如202004

-- 拼接动态SQL语句，显式转换为decimal后做减法
SET @sql = N'UPDATE tblSample 
             SET X = TRY_CONVERT(DECIMAL(10,2), [' + @colX1 + N']) - TRY_CONVERT(DECIMAL(10,2), [' + @colX2 + N']),
                 Y = TRY_CONVERT(DECIMAL(10,2), [' + @colY1 + N']) - TRY_CONVERT(DECIMAL(10,2), [' + @colY2 + N'])'

-- 执行动态SQL
EXEC sp_executesql @sql

验证示例数据 #

用你给出的例子测试：

• TRY_CONVERT(DECIMAL(10,2), '3565.24') - TRY_CONVERT(DECIMAL(10,2), '3461.36') = 103.88，正确更新X列
• TRY_CONVERT(DECIMAL(10,2), '3461.36') - TRY_CONVERT(DECIMAL(10,2), '3510.36') = -49.00，正确更新Y列

这个语句会逐行计算每行的X、Y列值，不会出现全为1的错误，而且能自动适配每月的列名。

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