SQL Server 2014按星期循环时间段规则匹配查询目标行

核心逻辑 #

无需逐周循环平移，直接通过日期差值计算整周偏移量，一次计算得到当前周期对应的时段范围，性能远高于循环写法，同时兼容跨天时段场景，自动规避会话星期配置差异导致的计算错误。

实现代码 #

-- 实际使用时可将@CurrentTime直接替换为GETDATE()，定义变量仅方便测试场景替换值
DECLARE @CurrentTime DATETIME = GETDATE();

SELECT 
    t.id,
    t.effectiveDate,
    t.expirationDate
FROM YourTable t -- 替换为实际业务表名
WHERE
    -- 过滤未到生效时间的记录
    t.effectiveDate <= @CurrentTime
    AND
    (
        CASE
            -- 计算当前周对应的时段起始点，判断是否已到时段开始时间
            WHEN DATEADD(WEEK, DATEDIFF(DAY, t.effectiveDate, @CurrentTime)/7, t.effectiveDate) <= @CurrentTime
            -- 已到开始时间则判断当前时间是否早于当前周的时段结束点
            THEN @CurrentTime < DATEADD(WEEK, DATEDIFF(DAY, t.effectiveDate, @CurrentTime)/7, t.expirationDate)
            -- 未到当前周时段开始时间则取上一周期的结束点判断（适配跨天场景）
            ELSE @CurrentTime < DATEADD(WEEK, DATEDIFF(DAY, t.effectiveDate, @CurrentTime)/7 - 1, t.expirationDate)
        END = 1
    )

逻辑说明 #

• 第一层过滤直接排除effectiveDate晚于当前时间的记录，满足「时段从生效日期开始才生效」的规则要求
• 通过整数除法计算当前时间和行生效日期的整周差：DATEDIFF(day, t.effectiveDate, @CurrentTime)/7，直接得到需要平移的周数，无游标、无循环，大表查询也能保持稳定性能
• 自动适配跨天场景：不管时段是当日结束还是跨多日结束（只要总时长不超过7天），只要当前时间落在平移后的起止区间内就会命中
• 不依赖DATEPART(WEEKDAY)计算星期值，完全规避@@DATEFIRST会话配置不同导致的星期计算错误

场景验证 #

对应需求给出的三个测试用例，代码返回结果完全符合预期：

1. 传入'2022-07-06 19:30:00.000'：id=1的记录周偏移为0，平移后时段为当日18:00-23:00，当前时间在区间内，正常返回
2. 传入'2022-07-30 01:00:00.000'：id=2的记录周偏移为3周，平移后时段为2022-07-29 22:00至2022-07-30 02:00，当前时间在区间内，正常返回
3. 传入'2022-06-30 19:00:00.000'：id=1的effectiveDate晚于当前时间，直接被过滤，无返回结果

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