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)) + '' >= ShiftStartTime 
and 
'' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + '' <= 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 <= @tn and @tn < ShiftEndTime
   or ShiftEndTime < ShiftStartTime and @tn < ShiftEndTime 
   or ShiftEndTime < ShiftStartTime and ShiftStartTime <= @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?.

StackExchange Database Administrators Q#228475, answer score: 7