I've got a query that is pulling back data based on time of day.
In the database, I have each "Channel" with a StartTime and and EndTime, done in military as a varchar. So, 12:00am = 0000, 3:00am = 0300, 4:00pm = 1600, 10:00pm = 2200, etc.
The query I have pasted below works so long as the StartTime is "before" the EndTime, as its using some basic less than greater than comparisons. So if a channel has a starttime of 3am and an endtime of 4pm, and I pass in 5am, 0300 < 0500 < 1600, so it displays. Where I'm running into logic issue is the night ones, which sort of "overflow" into the next day. So, if a channel is from 11pm to 4am, 2300 > 0400, so it wont show if I pass in a time of say midnight, or 0000.
I just need some help in tweaking the logic to deal with that type of rollover scenario. The below scenario assumes that the time that I'm passing in is 12:30am. Let's say I have one channel in the database set for a StartTime of 10:00pm (2200), and an endtime of 4:00am (0400).
declare @TimeOfDay varchar(200)
set @TimeOfDay = '0030'
CONVERT(INT,FeaturedStartTime) < CONVERT(INT,@TimeOfDay)
and CONVERT(INT,FeaturedEndTime) > CONVERT(INT,@TimeOfDay)
Try changing your condition to something like
WHERE (CONVERT(INT,FeaturedStartTime) < CONVERT(INT,@TimeOfDay) AND CONVERT(INT,FeaturedEndTime) > CONVERT(INT,@TimeOfDay) ) OR (CONVERT(INT,FeaturedStartTime) > CONVERT(INT,FeaturedEndTime) AND (CONVERT(INT,@TimeOfDay) > CONVERT(INT,FeaturedStartTime) OR CONVERT(INT,@TimeOfDay) < CONVERT(INT,FeaturedEndTime) ) )
We're basically adding another case in which the
StartTime is greater than the
EndTime but then the
searched time needs to be outside this interval (i.e. either greater than
StartTime or smaller than
NB: Mind the parentheses!