SQL Server 2017

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

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: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')
3 rows affected

select * from ShiftMaster


ShiftID
ShiftName
ShiftDescripiton
ShiftStartTime
ShiftEndTime


SHF1
Shift A
First Shift
07:15:01.0000000
15:45:00.0000000

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

SHF3
Shift C
Third Shift
00:15:01.0000000
07:15:00.0000000


select * , '' + cast(datepart(hh, getdate()) as char(2)) + ':' + cast(datepart(n, getdate()) as char(2)) + ':' + cast(datepart(ss, getdate()) as char(2)) + ''
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


ShiftID
ShiftName
ShiftDescripiton
ShiftStartTime
ShiftEndTime
(No column name)


SHF1
Shift A
First Shift
07:15:01.0000000
15:45:00.0000000
12:34:37

ShiftID	ShiftName	ShiftDescripiton	ShiftStartTime	ShiftEndTime
SHF1	Shift A	First Shift	07:15:01.0000000	15:45:00.0000000
SHF2	Shift B	Second Shift	15:45:01.0000000	00:15:00.0000000
SHF3	Shift C	Third Shift	00:15:01.0000000	07:15:00.0000000