文章目录
一、本年所有月份
--本年所有的月数
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
二、本月所有天数
--本月所有的天数 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