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(GETDATE() AS TIME);


(No column name)


14:35:06


CREATE FUNCTION dbo.GetShift(@Date DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @Time time = CAST(@Date AS TIME);
    
    RETURN
    CASE WHEN @Time >= '07:15:01' AND @Time < '15:45:00' THEN 'SHIFT1'
         WHEN @Time >= '00:15:01' AND @Time < '07:15:00' THEN 'SHIFT3'
         ELSE 'SHIFT2' END;
END
SELECT dbo.GetShift(GETDATE());


(No column name)


SHIFT1

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