Finding a Time Range

[cyberbhai]

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.

[jbisono]

Its hard to say without viewing that particular table structure, in what data type are you saving the hour in the database? i have seen people who use float type and others datetime.

[cyberbhai]

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)