如何判断一个时间段是否和另一个时间段冲突?

案例：预订会议室时判断提交预订的时间段是否与已经预订的时间段冲突

CREATE TABLE roombookinfo(

  id int NOT NULL,

  title nvarchar(10) NOT NULL,

  beginTime datetime NOT NULL,

  endTime datetime NOT NULL

)

select * from roomBookInfo

truncate table roomBookInfo

 

INSERT INTO roomBookInfo VALUES (1, '会议一', '2010-10-15 00:01:00', '2010-10-15 00:03:00');

INSERT INTO roomBookInfo VALUES (2, '会议二', '2010-10-15 00:05:00', '2010-10-15 00:08:30');

INSERT INTO roomBookInfo VALUES (3, '会议三', '2010-10-16 00:12:00', '2010-10-17 00:15:00');

 

 

--网上找的解决方案

declare @bTime nvarchar(50)

declare @eTIme nvarchar(50)

set @bTime='2010-10-13 00:04:30'

set @eTIme='2010-10-18 00:07:30'

select * from roombookinfo where

((@bTime>=beginTime and @bTime<endTime) or(@bTime<endTime and @eTIme>endTime) or(@bTime<=beginTime and @eTIme>beginTime))

 

 

--根据网上打的资料自己写的（显然没有上面的精辟）

--declare@bTime nvarchar(50)

--declare@eTIme nvarchar(50)

set @bTime='2010-10-12 00:04:30'

set @eTIme='2010-10-18 00:09:30'

SELECT * FROM roombookinfo WHERE

(@bTime between begintime AND endTime) or(@eTIme between begintime AND endTime)

or(begintime between @bTime AND @eTIme) or(endTime between @bTime AND @eTIme)

 

 

 

--其它的用法

declare @a nvarchar(50)

declare @b nvarchar(50)

set @a='2010-10-15 00:04:30'

set @b='2010-10-17 00:09:30'

SELECT CASE(SELECT COUNT(*) FROM roombookinfo WHERE

(@a between begintime AND endTime) or(@b between begintime AND endTime)

or(begintime between @a AND @b) or(endTime between @a AND @b)

)

    WHEN 0 THEN '会议未创建'

    WHEN 1 THEN '有一个会议'

ELSE

   '有很多重复的会议'

END