SQL Server生成两日期区间年、ISO周及周起止表的跨年周异常如何处理

原代码出错的核心原因是分组时使用了自然年而非ISO周对应的ISO年，导致跨自然年的同一ISO周被拆分到两个分组中，才会出现重复的第53周记录。

修正后完整代码（兼容SQL Server 2008及以上版本） #

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd) + 1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT 
           -- 计算日期所属的ISO年，替代原来的自然年取值
           YEAR(DATEADD(DAY, 26 - DATEPART(ISO_WEEK, report_date), report_date)) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           MIN(report_date)                AS week_beginning_date,
           MAX(report_date)                AS week_ending_date
      FROM report_dates
  GROUP BY 
           YEAR(DATEADD(DAY, 26 - DATEPART(ISO_WEEK, report_date), report_date)), 
           DATEPART(ISO_WEEK, report_date)
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

版本适配说明 #

如果使用SQL Server 2022及更高版本，可以直接调用内置的ISO_YEAR函数简化写法，替换report_year的计算逻辑即可：

-- SQL Server 2022及以上版本report_weeks片段简化写法
report_weeks AS (
    SELECT 
           DATEPART(ISO_YEAR, report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           MIN(report_date)                AS week_beginning_date,
           MAX(report_date)                AS week_ending_date
      FROM report_dates
  GROUP BY 
           DATEPART(ISO_YEAR, report_date),
           DATEPART(ISO_WEEK, report_date)
)

修改后2020年第53周会正确返回2020-12-28到2021-01-03的完整区间，不会再出现拆分的错误记录。

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