基于日期范围和时间范围的DateTime筛选

看起来逻辑有点复杂,正如你所期望的那样,如果 @fromtime 大于 @totime 则它充当两个独立的滤波器。

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    
        CAST(CreateDate AS datetime2) AS 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  
    r.CreateDate >= CAST(@fromdate AS datetime2)
    AND r.CreateDate < DATEADD(day, 1, CAST(@todate AS datetime2))
    AND
    (
      (
        @fromtime < @totime
        AND CAST(r.CreateDate AS time) >= @fromtime 
        AND CAST(r.CreateDate AS time) <= @totime
      ) OR
      (
        @fromtime > @totime
        AND NOT
        (
              CAST(r.CreateDate AS time) >= @fromtime 
          AND CAST(r.CreateDate AS time) <= @totime
        )
      )
    );

db<>fiddle

分离datetime是这里的问题,如果日期和时间是分开的,Where子句将永远不会满足。 你可以试试这样的东西。

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 
    CAST(r.CreateDate AS time) 
FROM 
    cte AS r
WHERE  
 CAST(r.CreateDate AS datetime2) >= CONVERT( Datetime,CAST(@fromdate AS DATETIME) + CAST(@fromtime AS DATETIME))
 AND CAST(r.CreateDate AS datetime2) <= CONVERT( Datetime,CAST(@todate AS DATETIME) + CAST(@totime AS DATETIME))

我认为,查询将返回2024年9月13日和2024年09月14日20:00至2:00至午夜之间的所有行。
在这种情况下,分别设置日期和时间是合乎逻辑的。

例如,如果@fromtime='20:00'和@totime='02:00',则筛选器应返回行
从2024年09月13日20:00:00到2024年9月14日02:00:00
从2024年9月14日20:00:00到2024年09月14日23:59:59

如果@fromtime为'02:00',@totime='20:00',则筛选器应返回行

从2024年9月13日02:00:00到2024年09月13日20:00:00
从2024年9月14日02:00:00到2024年09月14日20:00:00。

SELECT CreateDate
FROM cte as r
WHERE CAST(r.CreateDate AS date) between @fromdate and @todate
 and (
   (@toTime<=@fromtime 
     and (CAST(r.CreateDate AS time) >=@fromtime 
     or CAST(r.CreateDate AS time)<=@totime )
   )
  or((@toTime>=@fromtime 
     and (CAST(r.CreateDate AS time) >=@fromtime 
        and CAST(r.CreateDate AS time)<=@totime )
   )

fiddle