Sql 在SELECT中将日期格式转换为双周时段

(If using SQL Server 2022 or later, see the much simpler answer from Charlieface.)个

给定定义任何两周时段的第一个星期一的参考日期，您可以计算天数差，然后计算FLOOR(diff / 14.0)以获得时段编号，或计算((diff % 14) + 14) % 14以计算相对于包含两周时段的开始星期一的天数.

要处理负偏移，需要使用FLOOR(diff / 14.0)，而不是只使用diff / 14.同样，((diff % 14 + 14) % 14中的双模数也需要将负数映射到所需的0..13范围.

DECLARE @ReferenceDate DATE = '2023-10-02' -- Reference Monday

;WITH CTE_Dates AS (
    SELECT CONVERT(DATETIME, '2023-09-01 13:45') Dt
    UNION ALL
    SELECT DATEADD(day, 1, D.Dt) AS Dt
    FROM CTE_Dates D
    WHERE D.Dt < '2023-10-31'
)
SELECT
    D.Dt,
    DATENAME(weekday, D.Dt) AS WeekDay,
    DATEDIFF(day, @ReferenceDate, D.Dt) AS DayDiff,
    FLOOR(DATEDIFF(day, @ReferenceDate, D.Dt) / 14.0) AS BucketNo,
    ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14 AS BucketDay,
    CONVERT(DATE, DATEADD(day, - ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14, D.Dt)) AS BucketStartDt,
    CONVERT(DATE, DATEADD(day, 13 - ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14, D.Dt)) AS BucketEndDt
FROM CTE_Dates D
OPTION (MAXRECURSION 1000)

部分结果:

请参见this db<>fiddle.

最初的帖子和随后的 comments 对于哪些星期一构成为期两周的桶的开始或中间并不一致，但可以根据需要更改@ReferenceDate个，以适应最终决定.(今天甚至不需要是星期一.)