i have to search based on opening and closing times of shops. e.g. opening time of one is from 9 am to 11 pm another can be 5 am to nextday 2 am. e.g of sample data below

ShopID    open time     close time
shop1	  10am   	11pm
shop2	  6am	        10pm
shop3	  6am	        12am
shop4	  4am	        2am 
shop5     12am          12am //open 24 hours

how can i write the query to find out which shops are open between 6 am and 3am nextday. The query should only return shop5. What is the best way to resolve the problem. bascially from the query users can find which shops are open at what time

6 Years
Discussion Span
Last Post by munna_001

thanks for the reply but the opening times are not dependent on date or day


thanks adam thats one of the main issue i am facing handling next day. any suggestions


I thought I just answered this. Change your fields to datetime, use date 1/1/1900 and the appropriate time. When you want to enter next day use Jan 2nd.

When you want to query for stores if the closing time is less than the opening time, then add a day to the closing datetime.

I'm pretty sure that the logic in the query

select * from calendar 
where date = @meeting_date 
and resource = @resource 
and (start_time < @start_time and end_time > @start_time 
or start_time < @end_time and end_time > @end_time

will work just fine for your case as well.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.