SQLServer获取本年所有月份，本月所有天数

文章目录

• 前言
• 一、本年所有月份
• 二、本月所有天数

---

一、本年所有月份

--本年所有的月数
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