是不是我考虑复杂了?
原帖如下:http://topic.csdn.net/u/20091019/09/f2b7d5c2-bb97-44bb-9d52-b1d734abd007.html?85143
- SQL code
create table #t(BeginTime datetime,EndTime datetime)insert #t select'2009-01-05','2009-11-01' union all select -- '2009-10-1' between BeginTime and EndTime '2009-01-05','2009-09-01' union all select -- '2009-1-1' between BeginTime and EndTime'2000-01-05','2009-11-01' union all select -- '2009-1-1' between BeginTime and EndTime '2009-10-1' between BeginTime and EndTime '2009-11-05','2009-11-10' union all select -- '2009-01-05','2009-08-01' -- BeginTime between '2009-1-1' and '2009-10-1' EndTime between '2009-1-1' and '2009-10-1' select * from #twhere '2009-1-1' between BeginTime and EndTime or '2009-10-1' between BeginTime and EndTime /*BeginTime EndTime ------------------------------------------------------ ------------------------------------------------------ 2009-01-05 00:00:00.000 2009-11-01 00:00:00.0002000-01-05 00:00:00.000 2009-11-01 00:00:00.000(所影响的行数为 2 行)*/select * from #twhere BeginTime between '2009-1-1' and '2009-10-1' or EndTime between '2009-1-1' and '2009-10-1'/*BeginTime EndTime ------------------------------------------------------ ------------------------------------------------------ 2009-01-05 00:00:00.000 2009-11-01 00:00:00.0002009-01-05 00:00:00.000 2009-09-01 00:00:00.0002009-01-05 00:00:00.000 2009-08-01 00:00:00.000(所影响的行数为 3 行)*/select * from #twhere '2009-1-1' between BeginTime and EndTime or '2009-10-1' between BeginTime and EndTime or BeginTime between '2009-1-1' and '2009-10-1' or EndTime between '2009-1-1' and '2009-10-1'/*BeginTime EndTime ------------------------------------------------------ ------------------------------------------------------ 2009-01-05 00:00:00.000 2009-11-01 00:00:00.0002009-01-05 00:00:00.000 2009-09-01 00:00:00.0002000-01-05 00:00:00.000 2009-11-01 00:00:00.0002009-01-05 00:00:00.000 2009-08-01 00:00:00.000(所影响的行数为 4 行)*/drop table #t
------解决方案--------------------
- SQL code
declare @sdate datetimedeclare @edate datetimeset @sdate = '2009-1-1'set @edate = '2009-10-1'select m.* from tb m , (select dateadd(dd,num,@sdate) dtfrom (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) awhere dateadd(dd,num,@sdate)<[email protected]) nwhere n.dt between m.BeginTime and m.EndTime
------解决方案--------------------
如果是说BeginTime and EndTime 的时间在'2009-1-1' and '2009-10-1' 之内.
select * from #t
where '2009-1-1' between BeginTime and EndTime
or '2009-10-1' between BeginTime and EndTime
------解决方案--------------------
觉得这个准确点。
- SQL code
select * from #twhere '2009-1-1' between BeginTime and EndTime or '2009-10-1' between BeginTime and EndTime or BeginTime between '2009-1-1' and '2009-10-1' or EndTime between '2009-1-1' and '2009-10-1'
------解决方案--------------------
方法,挺多!