【SQL语句】获取本年所有月份

with t as
(
select convert(varchar(7),dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)),120)  month
from
(select number from master..spt_values where type='P'AND number>=0 and number<=12) t
where year(dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)))=year(getdate())
)
select month=ROW_NUMBER()OVER(ORDER BY t1.month),
        t1.month year_month
from t t1

　在SQL SERVER执行后，结果如下：

 

 

with t as(select convert(varchar(7),dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)),120)  monthfrom(select number from master..spt_values where type='P'AND number>=0 and number<=12) twhere year(dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)))=year(getdate()))select month=ROW_NUMBER()OVER(ORDER BY t1.month),        t1.month year_monthfrom t t1