比较mssql中的时间 [英] Compare time in mssql

问题描述

我有一个餐厅列表，其开放和关闭时间不同。

我想根据当前时间打开或关闭标签。



我有餐厅名称，开放时间，关闭时间



即TAjHOtel，上午10:00，下午07:00



我需要的结果如酒店名称，OPEN或关闭



我尝试过的方法：

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 

推荐答案

创建表格TestH

（

HName varchar（100），

OpenTime时间，

关闭时间

）



插入TestH值（'T1'，'11：00 AM'，'11：00 PM '）





从TestH中选择*

正如您在保存关闭时间时所看到的那样，它已被转换隐含地进入24小时格式



HName OpenTime关闭时间

T1 11：00：00.0000000 23：00：00.0000000



如果低于Proc返回1那么你可以将其标记为打开，否则关闭

Prcedure ---

创建程序[dbo]。[TestHSP ]

AS

BEGIN



声明@Flag位

声明@CurrentTime nvarchar（10）

声明@openTime时间

声明@closetime时间

选择@openTime =来自TestH的OpenTime

从TestH中选择@ closetime = CloseTime

set @ CurrentTime = CONVERT（varchar（10），CAS T（getdate（）AS TIME），100）

if（@ CurrentTime> @openTime和@ CurrentTime< @closetime）

set @ Flag = 1

其他

设置@ flag = 0

返回@flag



END

GO

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 ---
CREATE PROCEDURE [dbo].[TestHSP]
AS
BEGIN

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
else
set @flag=0
return @flag

END
GO

解决方案1很好，但有点过度设计。只需将现有查询更改为使用 CASE 语句

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

绝对不需要使用<$ c $处理时间时c> CONVERT - 您正在将时间对象更改为varchar对象，该对象仅用于减慢查询速度。实际上你可以完全取消变量...

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())

这篇关于比较mssql中的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！