BDate between @BDate and @EDate
or
EDate between @BDate and @EDate
or
@BDate between BDate and EDate
or
@EDate between BDate and EDate
or
EDate between @BDate and @EDate
or
@BDate between BDate and EDate
or
@EDate between BDate and EDate
直接小于开始时间--第四种情况 大于开始时间 大于结束时间 2008-05-13 07:30 2008-05-13 11:30 不允许
直接大于结束时间
因为开始时间肯定小于结束时间小于1 and 小于5 =小于1
优化一下:
BDate<=@EDate and EDate>=@BDate
求真子集可以,求非交集不行,严谨点 between and 改为大于和小于。
http://topic.csdn.net/u/20080421/22/b3f4edfe-937b-4b74-85a5-d218cf1a2174.html?seed=1687338846/*
有一个表如下:
SCode StartDate EndDate
002044 2008-04-01 2008-04-30
002044 2008-06-01 2008-06-15
002044 2008-08-01 2008-08-15
如果给出一个两个时间如:s1=2008-06-16,s2=2008-06-30,或者是s1=2008-04-1,s2=2008-04-15,时间段也可能在2008-04-01之前,或2008-08-15之后,如何判断这个时间段是否已在数据库中?(数据库的最小时间段是半个月,以15号为分界判断之).
哪个高手能帮一下忙,或提供一个思路?先谢过了!
*/create table tb(SCode varchar(10) , StartDate datetime , EndDate datetime)
insert into tb values('002044' ,'2008-04-01', '2008-04-30')
insert into tb values('002044' ,'2008-06-01', '2008-06-15')
insert into tb values('002044' ,'2008-08-01', '2008-08-15')
go--加入一临时表
SELECT TOP 8000 id = identity(int,0,1) INTO tmp FROM syscolumns a, syscolumns b
go--建立一存储过程
create procedure my_proc @dt1 datetime , @dt2 datetime , @return varchar(10) OUTPUT
as
begin
if exists
(
select m.* from
(select dt = dateadd(day , tmp.id , @dt1) from tmp where dateadd(day , tmp.id , @dt1) <= @dt2) m , tb n
where m.dt between n.startdate and n.enddate
)
set @return = '存在'
else
set @return = '不存在'
end
go--调用存储过程并返回结果
declare @return as varchar(10)exec my_proc '2008-06-16' , '2008-06-30' , @return OUTPUT
print @return
/*
不存在
*/exec my_proc '2008-04-01' , '2008-04-05' , @return OUTPUT
print @return
/*
存在
*/drop table tb,tmp
drop procedure my_proc
create table t(BDate datetime, EDate datetime)
insert t select '2008-05-13 07:30', '2008-05-13 15:30' --不允许
union all select '2008-05-13 09:30', '2008-05-13 11:30' --不允许
union all select '2008-05-13 09:30', '2008-05-13 12:30' --不允许
union all select '2008-05-13 07:30', '2008-05-13 11:30' --不允许
union all select '2008-05-14 07:30', '2008-05-14 11:30' --允许go
--数据表test
create table test(ID int identity(1,1), BDate datetime, EDate datetime)
insert test select '2008-05-13 08:30:00.000', '2008-05-13 12:00:00.000' --已有数据go--查询允许插入的数据
select * from t
where not exists(select 1 from test where t.BDate<=EDate and t.EDate>=BDate)/*
BDate EDate
----------------------- -----------------------
2008-05-14 07:30:00.000 2008-05-14 11:30:00.000(1 row(s) affected)
*/drop table t, test