How to get Current Shift? Question

How can I retrieve the current shift record based on getdate() and which lies between the existing ShiftStartTime and ShiftEndTime as per the defined table and data?

It is possible get shift if the time reaches 12.00 AM?

Table


create table ShiftMaster 
(
ShiftID char(4), 
ShiftName char(7), 
ShiftDescripiton varchar(20), 
ShiftStartTime time, 
ShiftEndTime time 
)


Data


insert into ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift', '07:15:01', '15:45:00'), 
('SHF2', 'Shift B', 'Second Shift', '15:45:01', '00:15:00'),
('SHF3', 'Shift C', 'Third Shift', '00:15:01', '07:15:00')


Original Data (Screenshot)



What I Have Tried

 SELECT * FROM VHASM2.ShiftMaster 
 WHERE FORMAT(getdate(),'HH:mm:ss') between ShiftStartTime and ShiftEndTime;


dbfiddle and Alternate Statement

select * 
from ShiftMaster
where 
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' &gt;= ShiftStartTime 
and 
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' &lt;= ShiftEndTime


The dbfiddle so far.
You can separate the logic into 3 parts:


first, for "normal" shifts, that don't include midnight
and second and third for shifts that have midnight.


The query:

-- time now
declare @tn as time = getdate() ;

select *
from ShiftMaster
where ShiftStartTime &lt;= @tn and @tn &lt; ShiftEndTime
   or ShiftEndTime &lt; ShiftStartTime and @tn &lt; ShiftEndTime 
   or ShiftEndTime &lt; ShiftStartTime and ShiftStartTime &lt;= @tn 
  ;


Test in dbfiddle.uk.



Please also note that I used closed-open intervals and comparisons (avoiding the evil BETWEEN*), so the shift ('00:15:00', '07:15:00') stands for any time just of after 00:15 and before 07:15 (but not exactly 07:15). So every point in time should be in one and only one shift, assuming that you define the shifts correctly.

insert into ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift',
             '07:15:00', '15:45:00'),   -- notice the starts
('SHF2', 'Shift B', 'Second Shift',
             '15:45:00', '00:15:00'),   -- and the ends
('SHF3', 'Shift C', 'Third Shift',
             '00:15:00', '07:15:00') ;


With your method, there would be gaps of 1 second between shifts.

*: More info and detailed explanation about why to avoid BETWEEN with datetime types on the excellent blog post by Aaron Bertrand: What do BETWEEN and the devil have in common?.