SQL周数查询返回2022.52应为2021.52的错误原因排查

• TPF_year字段取值逻辑错误
  你当前TPF_year直接取了对应月份的自然年，完全没有遵循ISO周的年份规则。ISO周的年份和自然年不一定对齐，每年的最后几天和前几天很可能属于相邻年份的ISO周，你直接取year(dateadd(month, @period_number * +1-1, @next_end_month))自然会把2021年的ISO周51、52错归到2022年。
• TPF_period_number的年份计算逻辑错误
  你写的case判断datepart(ISO_WEEK, 日期) < 53就取当天自然年，这个逻辑完全不符合ISO周年份的判定规则。ISO周的年份判定标准是「该周的周四属于哪个自然年，这一周就属于哪一年的ISO周」，不是单纯看周数是否小于53。而且你计算年份用的日期是dateadd(week, @period_number * +1, @next_sunday)，计算周数用的是dateadd(week, @period_number -1, @next_sunday)，两个日期差了2周，周数和年份根本对不上，必然出现匹配错误。
• 周起止日期的计算逻辑存在冗余和不一致风险
  你计算开始和结束周期的公式分别用了@today和GETDATE()两个基准日期，如果这两个值不一样（比如变量赋值和执行时间跨天），会直接导致周区间错位。

SQL Server 2022及以上版本可以直接用DATEPART(ISO_YEAR, 日期)获取ISO周对应的年份，低版本可以用周内周四的自然年作为ISO周年份，修正后的插入逻辑如下：

-- 提前计算基准周的起止，避免循环内重复计算出现不一致
DECLARE @base_sunday DATE = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6)
DECLARE @base_monday DATE = DATEADD(day, -6, @base_sunday)

WHILE @period_number <= @nb_period
BEGIN 
  INSERT #T_period_futur
  (TPF_year,
   TPF_period_number,
   TPF_start_period,
   TPF_end_period
  )
  SELECT 
    -- 取当前周周四的自然年作为ISO周年份，兼容低版本SQL Server
    DATEPART(YEAR, DATEADD(DAY, 3, DATEADD(WEEK, @period_number -1, @base_monday))),
    -- 年份和周数用同一周的日期计算，避免错位
    CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(DAY, 3, DATEADD(WEEK, @period_number -1, @base_monday)))) 
      + '.' + RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(ISO_WEEK, DATEADD(WEEK, @period_number -1, @base_sunday))), 2),
    -- 周一开始
    DATEADD(WEEK, @period_number -1, @base_monday),
    -- 周日结束
    DATEADD(WEEK, @period_number -1, @base_sunday)
  ;
  SELECT @period_number = @period_number + 1;
END;

如果追求更高的执行效率，也可以把循环替换为递归CTE生成1-12的数字序列，一次性插入所有周数据，无需循环迭代。

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