日期时间段分割处理！
根据日期时间段，得到每周的日期时间段。
例如：
      数据:
      001   ,   2007/04/16   ,   2007/04/29  
      002   ,   2007/04/23   ,   2007/04/29

      得到数据：
      001   ,   2007/04/16   ,   2007/04/22  
      001   ,   2007/04/23   ,   2007/04/29  
      002   ,   2007/04/23   ,   2007/04/29

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


select id ,StartDate, '2007/04/22 ' as EndDate
from table1
when StartDate < '2007/04/23 ' and EndDate> '2007/04/22 '

union
select id , '2007/04/23 ' as StartDate, EndDate
from table1
when StartDate < '2007/04/23 ' and EndDate> '2007/04/22 '

union
select id ,StartDate, EndDate
from table1
when StartDate> = '2007/04/23 ' or EndDate <= '2007/04/22 '


------解决方案--------------------
目前除了用游标，还没想到别的思路。。。
------解决方案--------------------
create function getTableG(@a varchar(3),@s smalldatetime,@e smalldatetime)
returns @t table(m varchar(3),n smalldatetime,p smalldatetime)
as
begin
declare @y table (a int identity(0,1),c smalldatetime,d smalldatetime,b char(1))

insert @y(c,d,b) select @s,null,null from syscolumns
update @y set d=dateadd(day,a,c)
delete from @y where d> @e
update @y set b= '1 ' where datepart(dw,d)=2
update @y set b= '7 ' where datepart(dw,d)=1
update @y set b= '1 ' where d=@s and b is null
update @y set b= '7 ' where d=@e and b is null
delete from @y where b is null
if exists(select 1 from @y where b=7 and d=@s)
insert @t select @a,@s,@s
if exists(select 1 from @y where b=1 and d=@e)
begin
insert @t select @a,@e,@e
insert @t select @a,d,null from @y where b=1 and d <> @e
end
else
insert @t select @a,d,null from @y where b=1
update @t set p=d from @t,@y where datediff(day,n,d)=6 and p is null
update @t set p=@e where p is null
return
end

go
declare @a table(a varchar(3),s smalldatetime,e smalldatetime)
insert @a select '001 ', '2007/04/16 ', '2007/04/29 '
union all select '002 ' , '2007/04/23 ', '2007/04/29 '
union all select '003 ' , '2007/04/24 ', '2007/05/29 '

declare @i varchar(3),@o smalldatetime,@e smalldatetime
declare @g table(a varchar(3),s smalldatetime,e smalldatetime)

declare cur cursor for
select * from @a order by a
open cur
fetch next from cur into @i,@o,@e
while @@fetch_status=0
begin
insert @g select * from dbo.gettableG(@i,@o,@e)
fetch next from cur into @i,@o,@e
end
close cur
deallocate cur
select * from @g


------解决方案--------------------
如果統計用的話,不防建一個每年的周時間表.

第十六周 16 2007-04-16 00:00:00 2007-04-22 00:00:00
........ 17 2007-04-23 00:00:00 2007-04-29 00:00:00

select a.name,b.startdate,b.enddate
from tmpTestdate a,tmpWeek b
where a.bgndate=b.startdate or a.enddate=b.enddate

result:

001 2007-04-16 00:00:00 2007-04-22 00:00:00