SQL Server指定日期范围内按特殊规则计算每周起止日期实现方案

以下代码可直接运行，完全匹配你要求的周计算规则：

-- 强制设置一周首日为周日，匹配样例规则
SET DATEFIRST 7;

-- 定义输入的日期范围参数
DECLARE @StartDate DATE = '2021-01-01', @EndDate DATE = '2022-12-31';

-- 生成连续日期序列
WITH AllDates AS (
    SELECT @StartDate AS CurrentDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CurrentDate)
    FROM AllDates
    WHERE CurrentDate < @EndDate
),
-- 标记周分界点：每年1月1日、周日为新周起始
WeekMark AS (
    SELECT 
        CurrentDate,
        YEAR(CurrentDate) AS YearNum,
        CASE 
            WHEN MONTH(CurrentDate) = 1 AND DAY(CurrentDate) = 1 THEN 1
            WHEN DATEPART(WEEKDAY, CurrentDate) = 1 THEN 1
            ELSE 0
        END AS IsNewWeekStart
    FROM AllDates
),
-- 生成周分组ID
WeekGroup AS (
    SELECT 
        CurrentDate,
        YearNum,
        SUM(IsNewWeekStart) OVER (ORDER BY CurrentDate ROWS UNBOUNDED PRECEDING) AS WeekGroupId
    FROM WeekMark
)
-- 聚合得到每周起止
SELECT 
    YearNum AS 所属年份,
    ROW_NUMBER() OVER (PARTITION BY YearNum ORDER BY WeekGroupId) AS 年度周序号,
    MIN(CurrentDate) AS 周起始日期,
    MAX(CurrentDate) AS 周结束日期
FROM WeekGroup
GROUP BY YearNum, WeekGroupId
ORDER BY 周起始日期
-- 日期范围超过100天需放开递归层级限制，0表示无限制
OPTION (MAXRECURSION 0);

规则适配说明 #

• 代码默认周周期为周日到周六，和你给出的样例输出完全对齐，如果需要调整周起始日，修改SET DATEFIRST的参数即可：1代表周一为周首日，7代表周日为周首日
• 自动处理年度首尾周截断：每年第一周固定从1月1日开始，最后一周固定到12月31日结束
• 无需依赖额外日历表，直接替换@StartDate和@EndDate为你需要的查询范围即可使用

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