SQL Server Time Comparison with Time over midnight

Question

I have a table in SQL Server which stores Start_Time (Time (7)) and End_Time (Time (7)).

The values stored in it are

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

I have a query which should return this row:

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

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

Can someone suggest a workaround to this without altering the columns type.

Thanks.

score 3 · Accepted Answer · answered Dec 20 '16 at 11:49

3

You need to include this option on your WHERE clause :

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

answered Dec 20 '16 at 11:49

sagi

40,026
6
59
84

score 1 · Answer 2 · answered Dec 20 '16 at 11:47

You can compare the start and end times and then use this in the comparison logic:

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);

SQL Server Time Comparison with Time over midnight

2 Answers2