declare @d1 datetime,@d2 datetime
set @d1='2009-03-01'
set @d2='2010-01-01'
;
with cte(d,d1) as(
select convert(varchar(2),datepart(mm,@d1)) as d,@d1 as d1
union all
select convert(varchar(2),datepart(mm,dateadd(mm,1,a.d1))) as d, dateadd(mm,1,a.d1) as d1
 from cte a
 where convert(varchar(7),a.d1,120)<convert(varchar(7),@d2,120)
)select * from cte
/*
d    d1
---- -----------------------
3    2009-03-01 00:00:00.000
4    2009-04-01 00:00:00.000
5    2009-05-01 00:00:00.000
6    2009-06-01 00:00:00.000
7    2009-07-01 00:00:00.000
8    2009-08-01 00:00:00.000
9    2009-09-01 00:00:00.000
10   2009-10-01 00:00:00.000
11   2009-11-01 00:00:00.000
12   2009-12-01 00:00:00.000
1    2010-01-01 00:00:00.000

(11 行受影响)
*/