/** 万年日 **/
declare @BegYymm char(6)
declare @EndYymm char(6)
declare @calendar table(週次 int,星期日 char(2),星期一 Char(2),星期二 Char(2),星期三 char(2),星期四 char(2),星期五 char(2),星期六 char(2),月份 char(10))
set @BegYymm='200501'
set @EndYymm='200512'
begin
declare @BegDate datetime
declare @EndDate datetime
declare @iWeek int
declare @DayOfWeek int
declare @NewRow bit
declare @NewMon bit
set @BegDate=Cast(@BegYymm+'01' as datetime)
set @EndDate=DateAdd(day,-1,DATEADD(month, 1, @Endyymm+'01'))
set @NewRow=0
set @NewMon=0
while @BegDate<@EndDate
begin
set @DayOfWeek=DATEPART(dw,@BegDate )
if day(@BegDate)=1 Set @NewMon=1
if @NewRow=0
begin
set @iWeek=(select DateName(Week,@BegDate))
if @NewMon=1
insert @Calendar (週次,星期日,星期一,星期二,星期三,星期四,星期五,星期六,月份)
Values(@iWeek,'','','','','','','',DatePart(mm,@BegDate))
else
insert @Calendar (週次,星期日,星期一,星期二,星期三,星期四,星期五,星期六,月份)
Values(@iWeek,'','','','','','','','')
set @NewRow=1
set @NewMon=0
end
if @DayOfWeek = 1 update @calendar set 星期日=DATEPART ( dd , @BegDate ) where 週次=@iWeek
if @DayOfWeek = 2 update @calendar set 星期一=DATEPART ( dd , @BegDate ) where 週次=@iWeek
if @DayOfWeek = 3 update @calendar set 星期二=DATEPART ( dd , @BegDate ) where 週次=@iWeek
if @DayOfWeek = 4 update @calendar set 星期三=DATEPART ( dd , @BegDate ) where 週次=@iWeek
if @DayOfWeek = 5 update @calendar set 星期四=DATEPART ( dd , @BegDate ) where 週次=@iWeek
if @DayOfWeek = 6 update @calendar set 星期五=DATEPART ( dd , @BegDate ) where 週次=@iWeek
if @DayOfWeek = 7
begin
update @calendar set 星期六=DATEPART ( dd , @BegDate ) where 週次=@iWeek
set @NewRow=0
end
set @BegDate=DATEADD(day, 1, @Begdate)
end
end
select * from @calendar
--rainboy QQ:43460043