1.本年所有月份
--本年所有的月数
with t as
(
select replace(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 id=ROW_NUMBER()OVER(ORDER BY t1.month),
t1.month
from t t1
2.本月所有天数
--本月所有的天数
with t as
(
select replace(convert(varchar(10),dateadd(DAY,t2.number,t1.day),120),'-','') day from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'
)
select id=ROW_NUMBER()OVER(ORDER BY t1.day),
t1.day
from t t1