跨日期时间段的SQL DateTime筛选问题：起始时间大于结束时间无结果

需要筛选跨多天的数据，且指定的时间段跨午夜（例如从20:00到次日02:00），当前查询因起始时间（20:00）大于结束时间（02:00）无法返回正确结果，且必须分开传入日期范围参数和时间范围参数。需要的时间范围具体为：2024-09-13的00:00-02:00、2024-09-13的20:00-24:00、2024-09-14的00:00-02:00这三个时段的所有数据。

原示例SQL #

DECLARE @fromdate date = '2024-09-13',
        @todate date = '2024-09-14',
        @fromtime time = '20:00:00',
        @totime time = '02:00:00'

;WITH cte AS 
(
    SELECT    
        CreateDate
    FROM  
        (VALUES ('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 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')) x (CreateDate)
)
SELECT 
    r.CreateDate
FROM 
    cte AS r
WHERE  
    CAST(r.CreateDate AS date) >= @fromdate 
    AND CAST(r.CreateDate AS date) <= @todate 
    AND CAST(r.CreateDate AS time) >= @fromtime 
    AND CAST(r.CreateDate AS time) <= @totime 

原查询问题 #

上述SQL将日期和时间的筛选条件用AND连接，导致跨天时间段（20:00-02:00）没有匹配的记录，返回空结果。

期望结果 #

尝试的另一种写法（错误结果） #

使用BETWEEN筛选连续时段，但仅返回2024-09-13 20:00到2024-09-14 02:00的连续数据，漏掉了2024-09-13凌晨的记录：

WHERE CAST(r.CreateDate AS datetime2) 
  BETWEEN 
     CAST('2024-09-13 20:00:00' AS datetime2)
     AND
     CAST('2024-09-14 2:00:00' AS datetime2)

错误结果 #

解决方案 #

核心逻辑是区分时间段是否跨天，分别处理筛选条件：

• 若起始时间<=结束时间（正常时段），直接判断时间在范围内
• 若起始时间>结束时间（跨天时段），判断时间>=起始时间 或者 <=结束时间

修改后的完整SQL：

DECLARE @fromdate date = '2024-09-13',
        @todate date = '2024-09-14',
        @fromtime time = '20:00:00',
        @totime time = '02:00:00'

;WITH cte AS 
(
    SELECT    
        CreateDate
    FROM  
        (VALUES ('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 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')) x (CreateDate)
)
SELECT 
    r.CreateDate
FROM 
    cte AS r
WHERE
    -- 日期范围筛选
    CAST(r.CreateDate AS date) BETWEEN @fromdate AND @todate
    -- 时间范围筛选，区分跨天/非跨天情况
    AND (
        (@fromtime <= @totime AND CAST(r.CreateDate AS time) BETWEEN @fromtime AND @totime)
        OR (@fromtime > @totime AND (CAST(r.CreateDate AS time) >= @fromtime OR CAST(r.CreateDate AS time) <= @totime))
    )

该SQL会返回符合期望的所有结果，同时支持分开传入日期和时间参数，兼容跨天和非跨天的时间段筛选。

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