go create proc test ( @ID int,--会议编号 @startdate datetiem,--开始时间 @enddate datetiem--结束时间 ) as --判断是否存在开始时间和结束时间 if exists( select 1 from 记录表 where startdate=@staredate or enddate=@enddate ) or exists(select 1 from 记录表 where startdate=@staredate and enddate=@enddate) print '时间已存在!' else inert 记录表 select @ID,@staredate,@enddate
CREATE TABLE #B ( MeetingRoom int, BeginTime datetime, EndTime datetime ) insert into #B select 1,'2012-05-24 10:00:00','2012-05-24 16:00:00'DECLARE @BeignTime datetime --查询开始时间 DECLARE @EndTime datetime --查询结束时间 DECLARE @MeetingRoom INT --查询的会议室 SET @BeignTime='2012-05-24 11:00:00' SET @EndTime='2012-05-24 13:00:00' SET @MeetingRoom=1 SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime) SELECT CASE WHEN COUNT(1)>0 THEN '已占用' ELSE '未占用' END FROM #B WHERE (@BeignTime BETWEEN BeginTime AND EndTime OR @EndTime BETWEEN BeginTime AND EndTime) AND MeetingRoom=@MeetingRoom--已占用 DROP TABLE #B
CREATE TABLE #B ( MeetingRoom int, BeginTime datetime, EndTime datetime ) insert into #B select 1,'2012-05-24 10:00:00','2012-05-24 16:00:00'DECLARE @BeignTime datetime --查询开始时间 DECLARE @EndTime datetime --查询结束时间 DECLARE @MeetingRoom INT --查询的会议室 SET @BeignTime='2012-05-24 00:00:00' SET @EndTime='2012-05-24 17:00:00' SET @MeetingRoom=1 SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime) SELECT CASE WHEN COUNT(1)>0 THEN '已占用' ELSE '未占用' END FROM #B WHERE (@BeignTime BETWEEN BeginTime AND EndTime OR @EndTime BETWEEN BeginTime AND EndTime OR BeginTime BETWEEN @BeignTime AND @EndTime OR EndTime BETWEEN @BeignTime AND @EndTime) AND MeetingRoom=@MeetingRoom DROP TABLE #B 刚没考虑这样的 '2012-05-24 00:00:00' '2012-05-24 17:00:00'
CREATE TABLE #B ( MeetingRoom int, BeginTime datetime, EndTime datetime ) insert into #B select 1,'2012-05-24 10:00:00','2012-05-24 16:00:00'DECLARE @BeignTime datetime --查询开始时间 DECLARE @EndTime datetime --查询结束时间 DECLARE @MeetingRoom INT --查询的会议室 SET @BeignTime='2012-05-24 00:00:00' SET @EndTime='2012-05-24 17:00:00' SET @MeetingRoom=1 SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime) SELECT CASE WHEN COUNT(1)>0 THEN '已占用' ELSE '未占用' END FROM #B WHERE (@BeignTime BETWEEN BeginTime AND EndTime OR @EndTime BETWEEN BeginTime AND EndTime OR BeginTime BETWEEN @BeignTime AND @EndTime OR EndTime BETWEEN @BeignTime AND @EndTime) AND MeetingRoom=@MeetingRoom DROP TABLE #B!
go
create proc test
(
@ID int,--会议编号
@startdate datetiem,--开始时间
@enddate datetiem--结束时间
)
as
--判断是否存在开始时间和结束时间
if exists(
select 1 from 记录表 where startdate=@staredate or enddate=@enddate
) or
exists(select 1 from 记录表 where startdate=@staredate and enddate=@enddate)
print '时间已存在!'
else
inert 记录表
select @ID,@staredate,@enddate
(
MeetingRoom int,
BeginTime datetime,
EndTime datetime
)
insert into #B
select 1,'2012-05-24 10:00:00','2012-05-24 16:00:00'DECLARE @BeignTime datetime --查询开始时间
DECLARE @EndTime datetime --查询结束时间
DECLARE @MeetingRoom INT --查询的会议室
SET @BeignTime='2012-05-24 11:00:00'
SET @EndTime='2012-05-24 13:00:00'
SET @MeetingRoom=1
SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime)
SELECT CASE WHEN COUNT(1)>0 THEN '已占用' ELSE '未占用' END FROM #B WHERE
(@BeignTime BETWEEN BeginTime AND EndTime OR
@EndTime BETWEEN BeginTime AND EndTime) AND MeetingRoom=@MeetingRoom--已占用
DROP TABLE #B
CREATE TABLE #B
(
MeetingRoom int,
BeginTime datetime,
EndTime datetime
)
insert into #B
select 1,'2012-05-24 10:00:00','2012-05-24 16:00:00'DECLARE @BeignTime datetime --查询开始时间
DECLARE @EndTime datetime --查询结束时间
DECLARE @MeetingRoom INT --查询的会议室
SET @BeignTime='2012-05-24 00:00:00'
SET @EndTime='2012-05-24 17:00:00'
SET @MeetingRoom=1
SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime)
SELECT CASE WHEN COUNT(1)>0 THEN '已占用' ELSE '未占用' END FROM #B WHERE
(@BeignTime BETWEEN BeginTime AND EndTime OR
@EndTime BETWEEN BeginTime AND EndTime OR
BeginTime BETWEEN @BeignTime AND @EndTime OR
EndTime BETWEEN @BeignTime AND @EndTime) AND MeetingRoom=@MeetingRoom
DROP TABLE #B
刚没考虑这样的 '2012-05-24 00:00:00' '2012-05-24 17:00:00'
我在想添加 18:00 到 18:30 的就不行了 BETWEEN 包含等于了
还有解么?
--SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime) 这一句就是用于这样的时间的
(
MeetingRoom int,
BeginTime datetime,
EndTime datetime
)
insert into #B
select 1,'2012-05-24 10:00:00','2012-05-24 16:00:00'DECLARE @BeignTime datetime --查询开始时间
DECLARE @EndTime datetime --查询结束时间
DECLARE @MeetingRoom INT --查询的会议室
SET @BeignTime='2012-05-24 00:00:00'
SET @EndTime='2012-05-24 17:00:00'
SET @MeetingRoom=1
SELECT @BeignTime=DATEADD(SECOND,1,@BeignTime)
SELECT CASE WHEN COUNT(1)>0 THEN '已占用' ELSE '未占用' END FROM #B WHERE
(@BeignTime BETWEEN BeginTime AND EndTime OR
@EndTime BETWEEN BeginTime AND EndTime OR
BeginTime BETWEEN @BeignTime AND @EndTime OR
EndTime BETWEEN @BeignTime AND @EndTime) AND MeetingRoom=@MeetingRoom
DROP TABLE #B!