SQL中如何为滚动日期列追加周六至周五计周、指定起始日为第1周的连续周数

原有实现的核心问题

• datepart(week, [table].[Date]) 按自然年维度计算周序，跨年时周数会重置，无法实现跨年度的连续递增计数
• 函数遵循系统配置的周起始规则（多数SQL环境默认周日/周一为周首日），无法将2020-06-27 ~ 2020-07-03自动识别为第1周，和业务定义的周边界完全不匹配

正确实现逻辑 #

自定义周数计算的核心是固定业务锚点、规避系统规则干扰：

• 以业务定义的第1周起始日2020-06-27（周六）为计算基准，每7天为一个完整周
• 通过计算目标日期与锚点的自然日间隔，按周为单位取整偏移量，得到连续的周序值，完全不受系统周配置、自然年周期的影响

计算代码 #

SELECT
  FLOOR(DATEDIFF(DAY, '2020-06-27', [table].[Date]) / 7.0) + 1 AS CustomWeek
FROM [table]

边界值校验 #

• 日期2020-06-27（第1周起点）：计算结果为1，匹配预期
• 日期2020-07-03（第1周终点）：计算结果为1，匹配预期
• 日期2020-07-04（第2周起点）：计算结果为2，匹配预期
• 跨年日期2021-01-02（第28周起点）：计算结果为28，无跨年后的周数重置，符合连续计数要求
• 早于锚点的日期2020-06-26：计算结果为0，符合偏移逻辑

注意事项 #

• 公式中使用7.0做除数是为了触发浮点数运算，配合FLOOR函数确保负偏移场景下的取整逻辑正确（整数除法会因向零取整导致结果偏差）
• 该计算逻辑不受会话级DATEFIRST配置影响，无需额外调整周起始设置即可直接运行

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