如何在SQL中实现跨午夜的动态时间范围过滤？

问题背景 #

现有带毫秒精度的datetime2类型数据：

2024-09-13 20:00:50.1319399
2024-09-13 00:07:42.3220570
2024-09-13 00:09:54.2842320
2024-09-13 00:14:46.4739434
2024-09-13 00:16:34.7590837
2024-09-14 00:25:54.0899006
2024-09-14 01:21:27.6672343
2024-09-13 20:00:50.1319399
2024-09-13 15:07:42.3220570
2024-09-13 12:09:54.2842320
2024-09-13 13:14:46.4739434
2024-09-13 14:16:34.7590837
2024-09-14 17:25:54.0899006
2024-09-14 18:21:27.6672343

需要筛选2024-09-13至2024-09-14期间，且时间在20:00至次日02:00的数据。原查询使用BETWEEN '20:00:00' AND '02:00:00'无法生效，因为BETWEEN要求左边界≤右边界，而跨午夜的时间范围不满足这个条件，同时需要支持动态时间范围（如15:00-18:00）。

原无效查询：

SELECT [r].[CreateDate]
FROM [Runners] AS [r]
WHERE CONVERT(date, [r].[CreateDate]) >= '2024-09-13'
AND CONVERT(date, [r].[CreateDate]) <= '2024-09-14'
AND CONVERT(time, [r].[CreateDate]) BETWEEN '20:00:00' AND '02:00:00'
ORDER BY CreateDate

解决方案 #

核心思路 #

根据时间范围是否跨午夜（开始时间>结束时间），拆分两种判断逻辑：

1. 跨午夜：匹配「时间≥开始时间」OR「时间≤结束时间」
2. 不跨午夜：直接使用BETWEEN匹配中间时间

同时优化日期条件，避免使用CONVERT(date, CreateDate)导致索引失效，改用>=和<的范围查询（处理毫秒精度问题）。

可动态调整的SQL代码 #

-- 定义动态参数
DECLARE @StartTime TIME = '20:00:00';
DECLARE @EndTime TIME = '02:00:00';
DECLARE @StartDate DATE = '2024-09-13';
DECLARE @EndDate DATE = '2024-09-14';

SELECT [r].[CreateDate]
FROM [Runners] AS [r]
WHERE 
    -- 日期范围：包含@StartDate到@EndDate的所有时刻（避免毫秒精度遗漏）
    [r].[CreateDate] >= CONVERT(DATETIME2, @StartDate)
    AND [r].[CreateDate] < DATEADD(DAY, 1, CONVERT(DATETIME2, @EndDate))
    -- 时间范围判断
    AND (
        -- 跨午夜场景：匹配当日开始时间后，或次日结束时间前
        (@StartTime > @EndTime AND (CONVERT(TIME, [r].[CreateDate]) >= @StartTime OR CONVERT(TIME, [r].[CreateDate]) <= @EndTime))
        -- 非跨午夜场景：直接匹配时间区间
        OR (@StartTime <= @EndTime AND CONVERT(TIME, [r].[CreateDate]) BETWEEN @StartTime AND @EndTime)
    )
ORDER BY [r].[CreateDate];

索引友好版优化 #

如果CreateDate字段有索引，建议避免对其做函数转换，改用日期+时间的直接拼接比较，以利用索引提升查询效率：

DECLARE @StartTime TIME = '20:00:00';
DECLARE @EndTime TIME = '02:00:00';
DECLARE @StartDate DATE = '2024-09-13';
DECLARE @EndDate DATE = '2024-09-14';

-- 计算拼接后的datetime2值
DECLARE @StartDateTime DATETIME2 = CONVERT(DATETIME2, @StartDate) + CONVERT(DATETIME2, @StartTime);
DECLARE @EndDayStart DATETIME2 = CONVERT(DATETIME2, @EndDate);
DECLARE @EndDateTime DATETIME2 = @EndDayStart + CONVERT(DATETIME2, @EndTime);

SELECT [r].[CreateDate]
FROM [Runners] AS [r]
WHERE 
    CASE 
        -- 跨午夜场景：匹配开始日的开始时间后，或结束日的结束时间前
        WHEN @StartTime > @EndTime THEN
            CASE WHEN [r].[CreateDate] >= @StartDateTime AND [r].[CreateDate] < DATEADD(DAY,1,@StartDateTime) THEN 1
                 WHEN [r].[CreateDate] >= @EndDayStart AND [r].[CreateDate] < @EndDateTime THEN 1
                 ELSE 0 END
        -- 非跨午夜场景：匹配完整的日期时间区间
        ELSE CASE WHEN [r].[CreateDate] BETWEEN @StartDateTime AND @EndDateTime THEN 1 ELSE 0 END
    END = 1
ORDER BY [r].[CreateDate];

结果验证 #

针对示例数据，上述查询会返回符合条件的记录：

2024-09-13 20:00:50.1319399
2024-09-13 20:00:50.1319399
2024-09-14 00:25:54.0899006
2024-09-14 01:21:27.6672343

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