Click here to Skip to main content
15,741,892 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have a list of restaurants with different opening and closing time.
I want to give label OPEN or close according to a current time.

I have restaurantname, opening time, closing time

i.e TAjHOtel, 10:00 AM , 07:00 PM

I required result like hotel name, OPEN or close

What I have tried:

select CONVERT(varchar(10),CAST(getdate() AS TIME),100)   --get current time

DECLARE @DayNumber bigint  
DECLARE @CurrentTime nvarchar(10) 
set @DayNumber =  datepart(dw,getdate())  --get current day in number

 set @CurrentTime = CONVERT(varchar(10),CAST(getdate() AS TIME),100)

select resto.RestaurantName,resto.FoodPrepareTime,ram.OpeningTime,ram.ClosingTime FROM Restaurant Resto join RestaurantAvailablityMaster 
RAM on resto.RestaurantId  = ram.RestaurantId where RAM.DayId = @DayNumber 
Updated 29-May-18 0:18am

Create table TestH
HName varchar(100),
OpenTime TIme,
CloseTime Time

Insert into TestH values('T1','11:00AM','11:00PM')

Select * from TestH
As you can see below while saving Closing Time it got converted into 24Hrs Format implicitly

HName OpenTime CloseTime
T1 11:00:00.0000000 23:00:00.0000000

IF below Proc return 1 then you can mark it as open otherwise close
Prcedure ---

Declare @Flag bit
Declare @CurrentTime nvarchar(10)
declare @openTime Time
declare @closetime Time
select @openTime = OpenTime from TestH
select @closetime=CloseTime from TestH
set @CurrentTime= CONVERT(varchar(10),CAST(getdate() AS TIME),100)
if(@CurrentTime>@openTime and @CurrentTime<@closetime)
set @Flag=1
set @flag=0
return @flag

Share this answer
CHill60 29-May-18 6:20am    
If you are going to use a stored procedure instead of a simple query or simple function then you should give the OP an example of how they have to use it to get the results they want - it's not a trivial exercise for them given their level of knowledge
Solution 1 is fine, but a little over-engineered. Just change your existing query to use a CASE statement
select resto.RestaurantName,resto.FoodPrepareTime,ram.OpeningTime,ram.ClosingTime , 
CASE WHEN @CurrentTime >= ram.OpeningTime AND @CurrentTime <= ram.ClosingTime THEN 'Open' ELSE 'Closed' ENDFROM #Restaurant Resto 
join #RestaurantAvailablityMaster RAM on resto.RestaurantId  = ram.RestaurantId 
where RAM.DayId = @DayNumber

There is absolutely no need to use the CONVERT when handling the times - you are changing a time object to a varchar object which only serves to slow down your query. In fact you could do away with the variables altogether...
select resto.RestaurantName,resto.FoodPrepareTime,ram.OpeningTime,ram.ClosingTime , 
CASE WHEN CAST(getdate() AS TIME) >= ram.OpeningTime AND CAST(getdate() AS TIME) <= ram.ClosingTime THEN 'Open' ELSE 'Closed' END
FROM #Restaurant Resto 
join #RestaurantAvailablityMaster RAM on resto.RestaurantId  = ram.RestaurantId 
where RAM.DayId = datepart(dw,getdate())
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900