时间戳

首页 > 解决方案 > 两个日期和两个时间列sql server之间的可用房间预订

sql - 两个日期和两个时间列sql server之间的可用房间预订

问题描述

我的表中有四列。

STARTDATE               STARTTIME   ENDDATE                 ENDTIME       ROOMCODE
2018-10-16 00:00:00.000 14:00       2018-10-16 00:00:00.000 18:00         CS0001
2018-10-16 00:00:00.000 18:00       2018-10-16 00:00:00.000 22:00         CS0001

我想检查预订是否可用

       select CONFERENCE_ROOM from COR_CONFERENCE_BOOKING  where 
(cast(STARTDATE as datetime) + cast(START_TIME as time) >= cast('2018-10-16' 
as datetime) + cast(DATEADD(MINUTE, 01, '14:00') as time) 
and cast(STARTDATE as datetime) + cast(START_TIME as time) < cast('2018-10- 
  16' as datetime) + cast('18:00' as time)) 
  or (cast(ENDDATE as datetime) + cast(END_TIME as time) >= cast('2018-10-16' 
   as datetime) + cast(DATEADD(MINUTE, 01, '14:00') as time) 
   and cast(ENDDATE as datetime) + cast(END_TIME as time) < cast('2018-10-16' 
  as datetime) + cast('18:00' as time)) 
    and CONFERENCE_ROOM='CS0001'

问题是我想选择数据，如果在通过日期和时间的选择查询中找到任何数据然后房间被预订，否则它是免费的。

请解决这个问题。它对我来说太复杂了。

标签： sqlsql-servertsql

解决方案

这是 MS SQL Server 的示例代码段。
我假设这是您的目标 RDBMS，因为示例 SQL 使用该 DATEADD 函数。

它只会返回表变量中的第一条记录。

因为第二条记录超出了范围。

declare @Table table (
 ID INT PRIMARY KEY IDENTITY(1,1), 
 STARTDATE date, 
 STARTTIME time, 
 ENDDATE date, 
 ENDTIME time, 
 CONFERENCE_ROOM varchar(6)
);

insert into @Table (STARTDATE, STARTTIME, ENDDATE, ENDTIME, CONFERENCE_ROOM) values
('2018-10-16','14:00:00','2018-10-16','18:00:00','CS0001'),
('2018-10-16','18:00:00','2018-10-16','22:00:00','CS0001');

select ID, CONFERENCE_ROOM 
from @Table t
where (CAST(STARTDATE AS DATETIME) + CAST(STARTTIME as DATETIME)) < (CAST(ENDDATE AS DATETIME) + CAST(ENDTIME as DATETIME))
  and (CAST(STARTDATE AS DATETIME) + CAST(STARTTIME as DATETIME)) >= CAST('2018-10-16 14:00' AS DATETIME)
  and (CAST(ENDDATE AS DATETIME) + CAST(ENDTIME as DATETIME))     <= CAST('2018-10-16 18:00' AS DATETIME)
  and CONFERENCE_ROOM = 'CS0001';

如果 STARTTIME 和 ENDTIME 是 VARCHAR 而不是 TIME 数据类型？
然后，为了避免尝试转换废话数据时出错，您可以改用 TRY_CAST。
（如果 TRY_CAST 在您的 MS SQL Server 版本上可用）因为当转换失败时，TRY_CAST 将返回 NULL 而不是错误。

例子：

select ID, CONFERENCE_ROOM 
from @Table t
where STARTDATE = ENDDATE
  and TRY_CAST(STARTTIME AS TIME) < TRY_CAST(ENDTIME AS TIME)
  and STARTDATE = CAST('2018-10-16' AS DATE)
  and TRY_CAST(STARTTIME AS TIME) >= CAST('14:00' AS TIME)
  and TRY_CAST(ENDTIME AS TIME)   <= CAST('18:00' AS TIME)
  and CONFERENCE_ROOM = 'CS0001';

推荐阅读