DateTime filtering based on a date range and a time range

I need to filter data for more than two days and at the same time, I need to filter different time periods. In this scenario, my query does not work because the start time is larger than the end time. For example, I want to filter the data from 23:00 to 2:00 and definitely because 23 is greater than 2, no data is returned.

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 date) >= @fromdate 
    AND CAST(r.CreateDate AS date) <= @todate 
    AND CAST(r.CreateDate AS time) >= @fromtime 
    AND CAST(r.CreateDate AS time) <= @totime 

I need data between 2024-09-13 20:00:00 and 2024-09-14 2:00:00

Result should be:

but my query returns nothing.

One point: I need to pass date and time separately

If I change the where clause like this:

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)

It still doesn't work its returns

but correct result is:

For clarity I need this range of data

It looks like the logic is a little more involved, as you are expecting that if @fromtime is greater than @totime then it acts as two independent filters.

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