如何在SQL Server中将日期区间按周六至周五划分周？

实现思路 #

1. 生成目标日期范围内的所有日期序列
2. 为每个日期计算其所属的周六起始周和周五结束周
3. 对周区间去重，并调整首尾周的起止日期以匹配给定的区间范围

完整SQL代码 #

WITH DateRange AS (
    -- 初始化起始日期
    SELECT CAST('2022-12-01' AS DATE) AS DateVal
    UNION ALL
    -- 递归生成后续日期，直到结束日期
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal < CAST('2022-12-30' AS DATE)
),
WeeklyIntervals AS (
    SELECT
        -- 计算该日期所属周的周六起始（基于固定周六'1900-01-06'，不受DATEFIRST设置影响）
        DATEADD(WEEK, DATEDIFF(WEEK, '1900-01-06', DateVal), '1900-01-06') AS WeekStart,
        -- 周结束为起始加6天（即周五）
        DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(WEEK, '1900-01-06', DateVal), '1900-01-06')) AS WeekEnd
    FROM DateRange
)
SELECT DISTINCT
    -- 确保周起始不早于给定区间的起始日期
    CASE WHEN WeekStart < '2022-12-01' THEN '2022-12-01' ELSE WeekStart END AS ActualWeekStart,
    -- 确保周结束不晚于给定区间的结束日期
    CASE WHEN WeekEnd > '2022-12-30' THEN '2022-12-30' ELSE WeekEnd END AS ActualWeekEnd
FROM WeeklyIntervals
ORDER BY ActualWeekStart;

代码说明 #

• DateRange CTE：通过递归方式生成2022-12-01到2022-12-30之间的所有日期。
• WeeklyIntervals CTE：使用固定基准周六1900-01-06计算每个日期所属的周起止，这种方式避免了DATEFIRST设置对星期计算的影响，保证结果稳定。
• 最终查询：通过DISTINCT去重得到唯一的周区间，再通过CASE语句调整首尾周的起止，确保完全落在给定的日期范围内。

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