Check if time is within range that crosses midnight boundary

Transact-SQL
1

I have a WHERE clause that checks to see if current time is between StartTime and EndTime (see below). Right now time values are stored in char(6) military time (1:30 PM stored as '133000')

WHERE
MyTab.TimeStart <= @nvaCurrentTime
AND
MyTab.TimeEnd >= @nvaCurrentTime

The problem is, if my table has a time range where start time is greater than end time, like between 8:00 PM and 8:00 AM (nighttime, spanning the midnight boundary), this criteria does not work. 9:00 PM logically falls within that span, but practically '080000' is not > '210000', so my second set of criteria fails. How can I make this work in T-SQL without creating temp tables and bringing full date-time into the picture? Ideally - by simply modifying the WHERE clause on this single SELECT statement

2 
WHERE 
	@nvaCurrentTime >= MyTab.TimeStart
	AND @nvaCurrentTime <= 
		CASE 
			WHEN MyTab.TimeStart > MyTab.TimeEnd THEN 
				CAST(CAST(MyTab.TimeEnd AS INT) + 240000 AS CHAR(6))
			ELSE
				MyTab.TimeEnd
			END

If you have any index on the TimeStart or TimeEnd (or even if you don't have), the following which should be logically equivalent to the query above might perform better.

WHERE 
	@nvaCurrentTime >= MyTab.TimeStart
	AND 
		CASE
			WHEN MyTab.TimeStart > MyTab.TimeEnd THEN 
				CAST( CAST(@nvaCurrentTime AS INT)- 240000 AS CHAR(6))
			ELSE
				@nvaCurrentTime
		END <= MyTab.TimeEnd
3

Unfortunately, that fails if @nvaCurrentTime is between midnight and TimeEnd (say, 5:00 AM in my example above)

4

Ok, this was an interesting exercise, but the issue actually went away on it's own - I just realized that my other filter was by day of the week, which makes this entire midnight-crossing logic moot. :joy: However, after thinking about it some more - for those who may take interest in this - it's possible to determine the actual number of hours between Start and End, then force Start to be '000000" and End to be (Start + number of hours), then determine the offset of Now from Start, and do a much more comfortable math comparison of a number falling within the range of '0' through 'x'. I just don't have the time nor the need to write the actual code right now, but that's the idea.
James - thanks for trying, man, appreciate it.