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

 

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