What you need is closer to
SELECT ShiftName FROM tblShiftName WHERE '17:15' BETWEEN STime AND ETime
But even that isn't going to be ideal, or even work in all cases.
The problem is twofold: firstly you are using strings to store numeric values which means that all comparisons are performed using character-by-character checks so the result of the whole comparison depends only on the first different character pair.
And secondly, your shifts overlap midnight, so your "end time" is sometimes earlier than your "start time".
Personally, I'd use a numeric value ("minutes since midnight" perhaps) and write an SP which does a more complicated check that just "between" which is about all you can do with the current system. I'd look at the current time and use that to decide which day you are most likely to be in, then check for shift, rather than a "dumb" check.