Finding a Time Range

Question

question

cyberbhai asked May 27, '10

Finding a Time Range

I have a table having shop details where shop opening and closing time are available. My need is to find shops that are open in a time range supplied.

For Example: Shop1 opens at 9 pm and closes next day at 4 am. Shop2 opens at 1 pm and closes at 12 am. Shop3 opens at 7 pm and closes at 10 pm. Shop4 opens at 10 am and closes at 1 pm.

I have to search for shops open between 6:30 pm to 8:30 pm. The result should be Shop2 and Shop3.

Search for Shops open between 12 pm to 2 pm will return Shop2 and Shop4.

Thanx in advance.

sql-server-2005

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-27T09:58:17Z

Squirrel 1 answered May 27, '10

declare @shops table            
(            
    shop        int,            
    open_tm     datetime,            
    close_tm    datetime            
)            
            
insert into @shops            
select  1, '21:00', '04:00' union all            
select  2, '13:00', '00:00' union all            
select  3, '19:00', '22:00' union all            
select  4, '10:00', '13:00'            
            
declare @open   datetime,            
    @close  datetime            
            
select  @open   = '12:00',            
    @close  = '14:00'            
            
; with cte            
as            
(            
    select  shop,            
        open_tm,            
        close_tm    = case when close_tm < open_tm then dateadd(day, 1, close_tm) else close_tm end            
    from    @shops            
)            
select  shop, open_tm, close_tm            
from    cte            
where   @open   between open_tm and close_tm            
or  @close  between open_tm and close_tm            
            
/*            
            
shop        open_tm                                                close_tm                                                           
----------- ------------------------------------------------------ ------------------------------------------------------             
2           1900-01-01 13:00:00.000                                1900-01-02 00:00:00.000            
4           1900-01-01 10:00:00.000                                1900-01-01 13:00:00.000            
            
(2 row(s) affected)            
            
*/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-04T07:36:19Z

cyberbhai answered Jun 4, '10

Converting the time to a number has done the trick for me.

For Example : '07:30 PM' in @StartTime is converted to '19000101193000000' by using replace(replace(replace(replace(convert(varchar(23), convert(datetime,@StartTime),21),'-',''),' ',''),':',''),'.','') and if the EndTime is greater than StartTime and is having AM, then it is considered Next Day.

declare @nStartTime int, @nEndTime int

set @nStartTime=replace(replace(replace(replace(convert(varchar(23), convert(datetime,@StartTime),21),'-',''),' ',''),':',''),'.','')

if right(@StartTime,2)='PM' and right(@EndTime,2)='AM' begin set @nEndTime=replace(replace(replace(replace(convert(varchar(23),dateadd(dd,1,convert(datetime,@EndTime),21)),'-',''),' ',''),':',''),'.','') end

else

begin set @nEndTime=replace(replace(replace(replace(convert(varchar(23), convert(datetime,@EndTime),21),'-',''),' ',''),':',''),'.','') end

Now the query can be Select * from TabName where ((replace(replace(replace(replace(convert(varchar(23), convert(datetime,StartTime),21),'-',''),' ',''),':',''),'.','') between @nStartTime and @nEndTime)

or

(replace(replace(replace(replace(convert(varchar(23), convert(datetime,EndTime),21),'-',''),' ',''),':',''),'.','')) between @nStartTime and @nEndTime))

and

(replace(replace(replace(replace(convert(varchar(23), convert(datetime,StartTime),21),'-',''),' ',''),':',''),'.','') <> @nEndTime)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question