# Check if time is within range that crosses midnight boundary

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

``````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``````

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

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. 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.