So the following query is generally working except for the above time range :

select TOP(1) CONVERT(DECIMAL(18,2),REPLACE(CONVERT(VARCHAR(5),CONVERT(DATETIME,EndTime),108),':','.'))-CONVERT(DECIMAL(18,2),REPLACE(CONVERT(VARCHAR(5),CONVERT(DATETIME,@DAte),108),':','.')) FROM ProductionShift WHERE CAST(@date AS time(5)) BETWEEN CAST(replace(convert(varchar,@date,110),'/','-')+' '+StartTime+':00.000' AS time(5)) AND CAST(replace(convert(varchar,@date,110),'/','-')+' '+EndTime+':00.000' AS time(5))

The columns StartTime and EndTime are varchars(ie 06:00, 13:59)!They can't be changed. My thought was to short of 'construct' the date so can check the range properly. Like I mentioned, it works for all the shifts EXCEPT the night one!!

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.