这样可以不？
create proc Te
@bdate datetime,
@edate datetime
as
declare @t varchar(8000)
while(datediff(mm,@bdate,@edate)>=0)
begin
set @t=isnull(@t+' union all select ','select dt=')+''''+datename(mm,@bdate)+''' '
set @bdate=dateadd(mm,1,@bdate)
end
exec(@t)
go
exec te '2009-03-01','2010-01-23'
select datepart(m,@endDate)把这两个数传到前台，由前台显示之间的月份
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 行受影响)
*/
如只要月,可在最后一句上用 select d from cte
declare @d1 datetime,@d2 datetime
set @d1='2009-03-01'
set @d2='2010-01-01'
;
with cte(d,d1) as(
select right(convert(varchar(7),@d1,120),2) as d,@d1 as d1
union all
select right(convert(varchar(7),dateadd(mm,1,a.d1),120),2) 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
---- -----------------------
03 2009-03-01 00:00:00.000
04 2009-04-01 00:00:00.000
05 2009-05-01 00:00:00.000
06 2009-06-01 00:00:00.000
07 2009-07-01 00:00:00.000
08 2009-08-01 00:00:00.000
09 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
01 2010-01-01 00:00:00.000(11 行受影响)
*/
set @d1='2009-03-01'
set @d2='2010-01-01'select month(dateadd(mm,fid-1,@d1)) mon,dateadd(mm,fid-1,@d1) date
from (
select top 100 percent fid=(
select count(*) from sysobjects where id <=a.id
)
from sysobjects a
order by id
) t
where t.fid<=datediff(mm,@d1,@d2)+1mon date
----------- -----------------------
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 行受影响)
set @d1='2009-03-01'
set @d2='2019-03-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 cted 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
2 2010-02-01 00:00:00.000
3 2010-03-01 00:00:00.000
4 2010-04-01 00:00:00.000
5 2010-05-01 00:00:00.000
6 2010-06-01 00:00:00.000
7 2010-07-01 00:00:00.000
8 2010-08-01 00:00:00.000
9 2010-09-01 00:00:00.000
10 2010-10-01 00:00:00.000
11 2010-11-01 00:00:00.000
12 2010-12-01 00:00:00.000
1 2011-01-01 00:00:00.000
2 2011-02-01 00:00:00.000
3 2011-03-01 00:00:00.000
4 2011-04-01 00:00:00.000
5 2011-05-01 00:00:00.000
6 2011-06-01 00:00:00.000
7 2011-07-01 00:00:00.000
8 2011-08-01 00:00:00.000
9 2011-09-01 00:00:00.000
10 2011-10-01 00:00:00.000
11 2011-11-01 00:00:00.000
12 2011-12-01 00:00:00.000
1 2012-01-01 00:00:00.000
2 2012-02-01 00:00:00.000
3 2012-03-01 00:00:00.000
4 2012-04-01 00:00:00.000
5 2012-05-01 00:00:00.000
6 2012-06-01 00:00:00.000
7 2012-07-01 00:00:00.000
8 2012-08-01 00:00:00.000
9 2012-09-01 00:00:00.000
10 2012-10-01 00:00:00.000
11 2012-11-01 00:00:00.000
12 2012-12-01 00:00:00.000
1 2013-01-01 00:00:00.000
2 2013-02-01 00:00:00.000
3 2013-03-01 00:00:00.000
4 2013-04-01 00:00:00.000
5 2013-05-01 00:00:00.000
6 2013-06-01 00:00:00.000
7 2013-07-01 00:00:00.000
8 2013-08-01 00:00:00.000
9 2013-09-01 00:00:00.000
10 2013-10-01 00:00:00.000
11 2013-11-01 00:00:00.000
12 2013-12-01 00:00:00.000
1 2014-01-01 00:00:00.000
2 2014-02-01 00:00:00.000
3 2014-03-01 00:00:00.000
4 2014-04-01 00:00:00.000
5 2014-05-01 00:00:00.000
6 2014-06-01 00:00:00.000
7 2014-07-01 00:00:00.000
8 2014-08-01 00:00:00.000
9 2014-09-01 00:00:00.000
10 2014-10-01 00:00:00.000
11 2014-11-01 00:00:00.000
12 2014-12-01 00:00:00.000
1 2015-01-01 00:00:00.000
2 2015-02-01 00:00:00.000
3 2015-03-01 00:00:00.000
4 2015-04-01 00:00:00.000
5 2015-05-01 00:00:00.000
6 2015-06-01 00:00:00.000
7 2015-07-01 00:00:00.000
8 2015-08-01 00:00:00.000
9 2015-09-01 00:00:00.000
10 2015-10-01 00:00:00.000
11 2015-11-01 00:00:00.000
12 2015-12-01 00:00:00.000
1 2016-01-01 00:00:00.000
2 2016-02-01 00:00:00.000
3 2016-03-01 00:00:00.000
4 2016-04-01 00:00:00.000
5 2016-05-01 00:00:00.000
6 2016-06-01 00:00:00.000
7 2016-07-01 00:00:00.000
8 2016-08-01 00:00:00.000
9 2016-09-01 00:00:00.000
10 2016-10-01 00:00:00.000
11 2016-11-01 00:00:00.000
12 2016-12-01 00:00:00.000
1 2017-01-01 00:00:00.000
2 2017-02-01 00:00:00.000
3 2017-03-01 00:00:00.000
4 2017-04-01 00:00:00.000
5 2017-05-01 00:00:00.000
6 2017-06-01 00:00:00.000
7 2017-07-01 00:00:00.000
消息 530，级别 16，状态 1，第 15 行
语句被终止。完成执行语句前已用完最大递归 100。
5楼这个估计得改一下