SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) 
 Jun 22 2022 18:20:15 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table ShiftMaster 
(
ShiftID char(4), 
ShiftName char(7), 
ShiftDescripiton varchar(20), 
ShiftStartTime time, 
ShiftEndTime time 
) ;
insert into ShiftMaster 
values 
('SHF1', 'Shift A', 'First Shift', '07:15:00', '15:45:00'), 
('SHF2', 'Shift B', 'Second Shift', '15:45:00', '00:15:00'),
('SHF3', 'Shift C', 'Third Shift', '00:15:00', '07:15:00') ;
3 rows affected

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


ShiftID
ShiftName
ShiftDescripiton
ShiftStartTime
ShiftEndTime


SHF2
Shift B
Second Shift
15:45:00.0000000
00:15:00.0000000