Comparing SQL Server Time to Midnight

Question

I have a table in SQL Server where Start_Time (Time (7)) and End_Time (Time (7)) are stored.

The values ​​stored in it,

                  Start_Time        End_Time
                  15:00:00.0000000  01:00:00.0000000

I have a query that should return this string:

                 Select * from Table Where MyTime >= Start_Time and MyTime < 
                  End_Time

For a value of MyTime = 16:00:00, this should return a single row. But since End_Time is already after midnight, it does not return a single row. I cannot use the Date + Time column, because I use this Start_Time and End_Time table as a shared table, which I join to another table to get my data.

Can anyone suggest a workaround for this without changing the type of the columns.

Thank.

Answer 1

WHERE:

Select *
from Table
Where (Start_Time > End_time AND myTime >= start_time) -- Answer your case
   OR MyTime between start_time and end_time -- All the rest

Answer 2

, :

Select t.*
from Table t
Where (Start_time <= End_time and MyTime >= Start_Time and MyTime <  End_Time) or
      (Start_time > End_time and MyTime < Start_Time and MyTime >=  End_Time);