select a.begindate,a.enddate
from tb a
where not exists(select 1 from tb where a.begindate<>begindate and a.begindate between begindate and enddate)
from tb a
where not exists(select 1 from tb where a.begindate<>begindate and a.begindate between begindate and enddate)
插入记录时检验:
insert into tb select @t1,@t2 from tb where not exists(select 1 from tb where @t1 between 开始日期 and 截止日期 or @t2 between 开始日期 and 结束日期)
create table tab(star datetime,[end] datetime)
insert into tab select '2008-5-1','2008-5-30'
go
create trigger tri_tab on tab instead of insert,update
as
insert into tab select * from inserted a where not exists(select 1 from tab
where a.star between star and [end] or a.[end] between star and [end])go
--插入数据进行测试
insert into tab select '2008-5-30','2008-6-5' --失败
insert into tab select '2008-4-1','2008-5-2' --失败
insert into tab select '2008-5-5','2008-5-15' --失败
insert into tab select '2008-4-1','2008-4-5' --成功
insert into tab select '2008-6-1','2008-6-5' --成功
--修改测试
update tab set [end]='2008-05-01' where [end]='2008-4-5' --失败--查看结果
select * from tab
(
select id from x tT where (tT.开始日期<=timeTable.开始日期 and tT.截止日期<timeTable.截止日期)
or (tT.截止日期<=timeTable.开始日期 and tt.截止日期<timeTable.截止日期)
)