optionsix optionsix - 2 months ago 17
SQL Question

conditional logic for time of day in sql server query

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'

select
ChannelID, c.ChannelName
from Channels
where
CONVERT(INT,FeaturedStartTime) < CONVERT(INT,@TimeOfDay)
and CONVERT(INT,FeaturedEndTime) > CONVERT(INT,@TimeOfDay)

Answer

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

NB: Mind the parentheses!

Comments