现在有 1986-01-01~1986-03-10
1996-03-11~2008-09-30
......
2011-03-09~today
多个时间段
现在要求输入两个时间 startTime/endTime
如何判断startTime~endTime这个时间段不在上面时间段内,也不能有重叠
1996-03-11~2008-09-30
......
2011-03-09~today
多个时间段
现在要求输入两个时间 startTime/endTime
如何判断startTime~endTime这个时间段不在上面时间段内,也不能有重叠
--假设1986-01-01 存放于dt1 列,1986-03-10 存放于dt2 列:
--输入两个时间分别为 @starttime,@endtime
select * from tb where dt1>@endtime or dt2<@starttime
or endTime < col2 or startTime > col2其中col1代表你时间段的字段1,其中col2代表你时间段的字段2
create table tb(StartTime datetime,EndTime datetime)
insert into tb
select '1986-01-01','1986-03-10' union all
select '1996-03-11','1996-03-11' union all
select '2011-03-09','2011-09-15'
declare @time1 datetime
declare @time2 datetime
set @time1='1985-12-01'
set @time2='1985-12-02'
select * from tb where (StartTime>=@time1 and StartTime<=@time2) or (EndTime>=@time1 and EndTime<=@time2) or (StartTime<=@time1 and EndTime>=@time2)
create table tb(StartTime datetime,EndTime datetime)
insert into tb
select '1986-01-01','1986-03-10' union all
select '1996-03-11','1996-03-11' union all
select '2011-03-09','2011-09-15'
declare @time1 datetime
declare @time2 datetime
set @time1='2011-03-08'
set @time2='2011-09-18'
select * from tb where (StartTime>=@time1 and StartTime<=@time2) or (EndTime>=@time1 and EndTime<=@time2) or (StartTime<=@time1 and EndTime>=@time2) or (StartTime>=@time1 and EndTime<=@time2)
分四段判断
time1<=开始时间<=time2
time1<=结束时间<=time2
开始时间<=time1 time2>=结束时间
time1<=开始时间 结束时间>=time2