数据库时间段交集
给定一个时间区间(begin,end),数据库的一个时间区间(startTime,endTime)
1.最简单的列出各种情况
select * from table
where (endTime<=end and endTime>=begin)
or (startTime<=end and startTime>=begin)
or (begin<startTime and end>endTime)
2.使用between
select * from table where ((begin between startTime and endTime)
or (startTime between begin and end))
3.取反(重点是not)
//初始,就是送入的时间段在被比较时间段的两侧没有交集
select * from table where not ((endTime < begin) or (startTime > end))
//去掉not
select * from table where ((endTime>= begin) and ( startTime <=end))
测试代码:
create table test(
startTime date,
endTime date
)
insert test VALUES('2018-9-1','2018-9-10')
insert test VALUES('2018-8-30','2018-9-5')
insert test VALUES('2018-8-25','2018-8-30')
DROP TABLE test
SELECT * FROM test
declare @begin nvarchar(32)
declare @end nvarchar(32)
set @begin='2018-9-3'
set @end='2018-9-11'
select * from test where not ((endTime < @begin) or (startTime > @end))