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 #t
where '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.000
2000-01-05 00:00:00.000                                2009-11-01 00:00:00.000

（所影响的行数为 2 行）
*/

select * from #t
where  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.000
2009-01-05 00:00:00.000                                2009-09-01 00:00:00.000
2009-01-05 00:00:00.000                                2009-08-01 00:00:00.000

（所影响的行数为 3 行）
*/

select * from #t
where '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.000
2009-01-05 00:00:00.000                                2009-09-01 00:00:00.000
2000-01-05 00:00:00.000                                2009-11-01 00:00:00.000
2009-01-05 00:00:00.000                                2009-08-01 00:00:00.000

（所影响的行数为 4 行）
*/

drop table #t

declare @sdate datetime
declare @edate datetime
set @sdate = '2009-1-1'
set @edate = '2009-10-1'

select m.* from tb m , 
(
select 
    dateadd(dd,num,@sdate) dt
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
) n
where 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 #t
where '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'

------解决方案--------------------

方法，挺多！