opening time

[munna_001]

hi
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

[debasisdas]

First of all you need to store opening and closing time in date time format.

[munna_001]

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

[debasisdas]

if the time is stored as character, you will not be able to perform any calculation on the same.

[adam_k]

Perhaps this post will give you a hint:http://www.daniweb.com/software-development/vbnet/threads/374318/1611907#post1611907

With a few limitations you could possibly do the job with int or numeric for the time, but I don't see how you can handle the next day without datetime (Date part can be 1/1/1900)

[munna_001]

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

[adam_k]

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.

[munna_001]

thanks will try this tonite