How to get Current Shift?
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?.