如何在SELECT语句中将日期格式化为双周时间桶（周一至周日）

需求概述 #

现有表包含StartTime、EndTime及其他业务列，需要在SELECT语句中新增一个BiWeeklyBucket计算列，将每条记录的EndTime归入以「周一至周日」为周期的双周时间桶：

• 双周周期起始点固定为2018-10-01，第一个双周桶范围是2018-10-01至2018-10-14，对应标签为10/14/2018
• 规则示例：若EndTime在10月2日-10月15日之间，桶标签显示10/15/23；在10月16日-10月29日之间则显示10/29/23
• 要求仅通过SELECT语句实现，无需GROUP BY，仅为结果集新增计算列

现有单周时间桶实现 #

当前用于生成单周时间桶的SQL语句：

SELECT
    BunchOfOtherColumns
    ,FORMAT(DATEADD(DAY, 7 - DATEPART(WEEKDAY, EndTime), EndTime), 'MM/dd/yy') AS DateBucket
FROM ...

样本数据 #

预期输出 #

解决方案 #

以双周起始日期为桶标签（匹配样本输出） #

SELECT
    OtherColumns,
    FORMAT(
        DATEADD(
            DAY,
            -- 计算当前日期所在双周的起始偏移天数
            14 * FLOOR(DATEDIFF(DAY, '2018-10-01', CONVERT(DATE, EndTime)) / 14.0),
            '2018-10-01'
        ),
        'MM/dd/yyyy'
    ) AS BiWeeklyBucket
FROM YourTableName

以双周结束日期为桶标签（符合需求描述示例） #

如果需要以双周的最后一天作为桶标签（如需求中10月2日-10月15日对应10/15/23），使用以下语句：

SELECT
    OtherColumns,
    FORMAT(
        DATEADD(
            DAY,
            -- 计算当前日期所在双周的结束偏移天数
            14 * CEILING(DATEDIFF(DAY, '2018-10-01', CONVERT(DATE, EndTime)) / 14.0) - 1,
            '2018-10-01'
        ),
        'MM/dd/yyyy'
    ) AS BiWeeklyBucket
FROM YourTableName

逻辑说明 #

1. CONVERT(DATE, EndTime)：剥离EndTime的时间部分，仅保留日期进行计算
2. DATEDIFF(DAY, '2018-10-01', ...)：计算从起始日期到目标日期的总天数差
3. FLOOR(天数差 / 14.0) / CEILING(天数差 /14.0)：将天数差按14天为一组取整，分别得到双周的起始/结束序号
4. DATEADD：基于起始日期，加上计算出的偏移天数，得到双周的起始/结束日期
5. FORMAT：将日期格式化为需求的MM/dd/yyyy字符串格式

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