Extract record based on time value

Question

I'm using SQLServer 2016 and I have the table (TIMETABLE) with the follow data:

ID	Descrizione	OraInizio	OraFine
1	Morning	06:00:00	13:00:00
2	Afternoon	13:00:01	19:00:00
3	Night	19:00:01	05:59:59

Now I need to extract the ID from this table using a datetime so I wrote this query:

DECLARE @DATA AS DATETIME = '11-09-2023 00:30:00'
SELECT * 
FROM TIMETABLE
WHERE Convert(time(2), Convert(smalldatetime, @DATA)) BETWEEN ORAINIZIO AND ORAFINE

Now this query is not working: it should extract ID = 3 because time 00:30 is in the Night range.

How can I change my query to solve this issue?

Answer 1

You need to take into account cases when ORAINIZIO >= ORAFINE and therefore the times are reversed.

You also shouldn't use BETWEEN for comparisons, as noted here. Instead use >= AND <.

DECLARE @DATA AS DATETIME = '2023-09-11 00:30:00';
DECLARE @tim time(2) = CONVERT(time(2), @DATA);

SELECT * 
FROM TIMETABLE
WHERE (ORAINIZIO < ORAFINE  AND @tim >= ORAINIZIO AND @tim < ORAFINE)
   OR (ORAINIZIO >= ORAFINE AND (@tim < ORAFINE OR @tim >= ORAINIZIO);