NewbieProgrammer NewbieProgrammer - 2 months ago 7
SQL Question

Sql Server - Comparing time only in 24 hour format?

I want to compare time with a time-range and if that particular time lies with-in that time-range, then it should return a record.

For example, Table 'A' has :

+-----------+------+-------------+-----------+
| User_Code | Name | Shift_Start | Shift_End |
+-----------+------+-------------+-----------+
| 1 | ABC | 04:01:00 | 11:00:00 |
| 2 | DEF | 11:01:00 | 20:00:00 |
| 3 | XYZ | 20:01:00 | 04:00:00 |
+-----------+------+-------------+-----------+


Now I want to check whose shift was it at this specific datetime :
2016-09-26 02:51:59
. The SQL query should return the
User_Code = 3
.
Shift_Start
and
Shift_End
are of type
time
.

I have tried converting
2016-09-26 02:51:59
to time and then comparing with
shift_start
and
shift_end
using
between
and using
logical operator
but I am unable to get the desired result.

This has got me baffled. Its been an hour since I have tried coming up with a solution but I am unable to do so. Tried googling but got nothing there too. Any help will be appreciated. Using SQL Server 2012.

Answer

You need a more complicated where condition, because the shift times can be in either order.

So:

where ((shift_start < shift_end) and
       cast(@datetime as time) between shift_start and shift_end)
      ) or
      ((shift_start > shift_end) and
       cast(@datetime as time) not between shift_start and shift_end)
      )
Comments