如何生成两日期间按周划分的日期列表（周一为周起始）

需求说明 #

需要生成起始日期到结束日期之间的日期分段列表，规则如下：

• 每周以周一为起始，周日为结束
• 若分段的理论周日结束日期超过当月月末，则以月末日期作为该段结束，下月从1号重新开始分段
• 最终分段不得超过指定的总结束日期

之前尝试通过SET DATEFIRST调整周起始，但因函数内无法使用SET运算符导致失败，以下是无需修改DATEFIRST的解决方案。

解决方案：递归CTE实现 #

使用递归CTE（公共表表达式）生成所有符合规则的日期分段，通过日期函数直接计算周结束、月末日期，避免依赖DATEFIRST设置。

DECLARE @StartDate DATE = '2022-08-04', -- 转换为YYYY-MM-DD格式避免日期歧义
        @EndDate DATE = '2022-09-08';

WITH DateSegments AS (
    -- 初始化第一个分段
    SELECT
        SegmentStart = @StartDate,
        SegmentEnd = (
            SELECT MIN(dt)
            FROM (VALUES
                -- 计算当前起始日期对应的周日结束（无需DATEFIRST，直接基于默认周计算）
                (DATEADD(DAY, 7 - DATEPART(WEEKDAY, @StartDate) + 1, @StartDate)),
                -- 计算当前起始日期所在月的月末日期
                (DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + 1, 0))),
                -- 总结束日期
                (@EndDate)
            ) AS Dates(dt)
        )
    UNION ALL
    -- 递归生成后续分段
    SELECT
        SegmentStart = DATEADD(DAY, 1, ds.SegmentEnd),
        SegmentEnd = (
            SELECT MIN(dt)
            FROM (VALUES
                (DATEADD(DAY, 7 - DATEPART(WEEKDAY, DATEADD(DAY, 1, ds.SegmentEnd)) + 1, DATEADD(DAY, 1, ds.SegmentEnd))),
                (DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, 1, ds.SegmentEnd)) + 1, 0))),
                (@EndDate)
            ) AS Dates(dt)
        )
    FROM DateSegments ds
    WHERE DATEADD(DAY, 1, ds.SegmentEnd) <= @EndDate
)
-- 按指定格式输出结果
SELECT
    FORMAT(SegmentStart, 'dd-MM-yyyy') AS SegmentStart,
    FORMAT(SegmentEnd, 'dd-MM-yyyy') AS SegmentEnd
FROM DateSegments
ORDER BY SegmentStart;

代码解释 #

1. 日期计算逻辑：
   • 周日结束日期：通过DATEPART(WEEKDAY)获取当前日期的星期数（默认周日为1），计算出当前日期后第一个周日的日期。
   • 月末日期：先计算当前日期所在月的下月初第一天，再减1得到当月最后一天。
   • 实际结束日期：取「理论周日、月末日期、总结束日期」三者的最小值，确保分段不越界。
2. 递归CTE：从初始分段开始，每次以上一段的结束日期+1作为新分段的起始，重复计算结束日期，直到起始日期超过总结束日期为止。
3. 格式输出：使用FORMAT函数将日期转换为dd-MM-yyyy格式，匹配需求示例的输出样式。

验证结果 #

对于输入起始日期='04-08-2022'、结束日期='08-09-2022'，执行代码后将输出：

04-08-2022     08-08-2022
09-08-2022     15-08-2022
16-08-2022     22-08-2022
23-08-2022     29-08-2022
30-08-2022     31-08-2022
01-09-2022     04-09-2022
05-09-2022     08-09-2022

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