获取时间段内所有周次及其起讫日期

 

declare   @table   table (周次  int ,起始日期  varchar ( 10 ),结束日期  varchar ( 10 ))
declare   @date   varchar ( 10 ),
     @date_old   varchar ( 10 ),
     @date_End   varchar ( 10 ),
     @i   int ,
     @i_old   int
set   @date = ' 2011-01-03 '
set   @date_End = ' 2012-05-05 '

set   @date_old = @date
set   @i = datepart (week, dateadd ( day , - 1 , @date ))
set   @i_old = @i

while ( @date <= @date_End )
begin
     set   @i = datepart (week, dateadd ( day , - 1 , @date ))
     if ( @i   <>   @i_old )
     begin
         Insert   into   @table (周次,起始日期,结束日期)
         values ( @i_old , @date_old , Convert ( varchar ( 10 ), dateadd ( day , - 1 , @date ), 20 ))

         set   @i_old   =   @i
         set   @date_old   =   @date
     end

     set   @date = Convert ( varchar ( 10 ), dateadd ( day , 1 , @date ), 20 )
end

-- 查看
select   *   from   @table