declare @start int, @end int declare @tb table(mth int) set @start=200701 set @end=200709 while @start<=@end begin insert @tb select @start set @start=@start+1 endselect * from @tb /* mth ----------- 200701 200702 200703 200704 200705 200706 200707 200708 200709(9 row(s) affected) */
declare @Date1 nvarchar(8),@Date2 nvarchar(8) select @Date1='200701',@Date2='200709' select @Date1=@Date1+'01',@Date2=@Date2+'01' ;with c as (select cast(@Date1 as datetime) Date union all select dateadd(m,1,Date) from C where Date<@Date2 ) select convert(varchar(6),date,112)Date from cDate ------ 200701 200702 200703 200704 200705 200706 200707 200708 200709(9 個資料列受到影響)
--循环 declare @t table(dt varchar(10)) declare @star datetime,@end datetime set @star='200701'+'01' set @end='200709'+'01' while(@star<=@end) begin insert into @t select convert(varchar(6),@star,112) set @star=dateadd(mm,1,@star) end select * from @t --借助临时表 declare @star datetime,@end datetime set @star='200701'+'01' set @end='200709'+'01' select top 8000 id=identity(int,1,1) into # from syscolumns select convert(varchar(6),dateadd(mm,id,@star),112) from # where dateadd(mm,id,@star)<=@end
declare @tb table(mth int)
set @start=200701
set @end=200709
while @start<=@end
begin
insert @tb select @start
set @start=@start+1
endselect * from @tb
/*
mth
-----------
200701
200702
200703
200704
200705
200706
200707
200708
200709(9 row(s) affected)
*/
select @Date1='200701',@Date2='200709'
select @Date1=@Date1+'01',@Date2=@Date2+'01'
;with c as
(select cast(@Date1 as datetime) Date
union all
select dateadd(m,1,Date) from C where Date<@Date2
)
select convert(varchar(6),date,112)Date from cDate
------
200701
200702
200703
200704
200705
200706
200707
200708
200709(9 個資料列受到影響)
--循环
declare @t table(dt varchar(10))
declare @star datetime,@end datetime
set @star='200701'+'01'
set @end='200709'+'01'
while(@star<=@end)
begin
insert into @t select convert(varchar(6),@star,112)
set @star=dateadd(mm,1,@star)
end
select * from @t
--借助临时表
declare @star datetime,@end datetime
set @star='200701'+'01'
set @end='200709'+'01'
select top 8000 id=identity(int,1,1) into # from syscolumns
select convert(varchar(6),dateadd(mm,id,@star),112) from # where dateadd(mm,id,@star)<=@end