Date and time range filter in SQL

I have this data

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

and I want to filter by a specific date and time separately.

This is my query

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

If I comment time filter query return result but I need this range 20:00 - 02:00 am, I know 20:00 is greater than 2:00 The problem is this and I don't need 19:00 , 18:00 , ... .Please keep in mind that time range is dynamic For example, it can be 15:00 - 18:00 and... etc

How can I fix it?

You can do something like this:

DECLARE @fromdate date = '20240913'
, @todate date = '20240914'
, @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   CONVERT(date, [r].[CreateDate]) >= @fromdate
AND CONVERT(date, [r].[CreateDate]) <= @todate
AND CASE 
        WHEN @fromtime < @totime THEN IIF(CONVERT(time, r.createDate) BETWEEN @fromtime AND @totime, 1, 0)
        ELSE IIF(CONVERT(time, r.createDate) NOT BETWEEN @totime AND @fromtime , 1, 0)
    END = 1
ORDER BY CreateDate;

If fromtime is less than totime, you can do a regular BETWEEN from and to query. If fromtime is > than totime, you reverse the logic and check so that the time is not between TO and FROM time.

Note that if you want exclusive ranges (ie. 20:00 - 02:00 should not match 20:00), you have to change between to > and <, exercise to fix that is left for the reader.

Output: