请教一个关于判定预定时间是否冲突的sql语句关于预定时间的问题,如何判定本次预定时间和已预订的时间没有
请教一个关于判定预定时间是否冲突的sql语句
关于预定时间的问题,
如何判定本次预定时间和已预订的时间没有冲突?比如有一下预定信息
1预定 2008.11.4 9:30 ---2008.11.4 11:00
2.预定 2008.11.4 14:00---2008.11.4 16:30
现在有个新的预定信息要求预定 2008.11.4 12:00 ---2008.11.4 15:00
用什么sql语句能判定这个预定的时间段和别的预定有冲突?
[解决办法]
就这几种关系
- SQL code
|_____________________| a |______________| b|_________________| a |____________| b |_______________| a|____________| b|_________________| a|_________________| b |____________| a|___________________| bDECLARE @t TABLE(d1 DATETIME,d2 DATETIME)INSERT @t SELECT '2008.11.4 9:30' ,'2008.11.4 11:00'UNION ALL SELECT '2008.11.4 14:00','2008.11.4 16:30'DECLARE @d1 DATETIME,@d2 DATETIMESELECT @d1='2008.11.4 12:00',@d2='2008.11.4 15:00'SELECT * FROM @t WHERE d2 BETWEEN @d1 AND @d2 OR d1 BETWEEN @d1 AND @d2 OR @d1 BETWEEN d1 AND d2 OR @d2 BETWEEN d1 AND d2
[解决办法]
- SQL code
DECLARE @FROMDATE DATETIMEDECLARE @TODATE DATETIMESET @FROMDATE='2008-11-04 12:00'SET @TODATE='2008-11-04 15:00'CREATE TABLE #A (S_DATE DATETIME,C_DATE DATETIME)INSERT INTO #A SELECT '2008-11-04 9:30','2008-11-04 11:00'INSERT INTO #A SELECT '2008-11-04 14:00','2008-11-04 16:30'INSERT INTO #A SELECT '2008-11-04 11:30','2008-11-04 12:30'SELECT * ,(CASE WHEN @FROMDATE>S_DATE AND @FROMDATE<C_DATE THEN 'YES' ELSE 'NO' END) YES_NOFROM #ADROP TABLE #A/***2008-11-04 09:30:00.000 2008-11-04 11:00:00.000 NO2008-11-04 14:00:00.000 2008-11-04 16:30:00.000 NO2008-11-04 11:30:00.000 2008-11-04 12:30:00.000 YES