DECLARE @today DATETIME, -- 今天
@month_bg DATETIME, -- 本月初
@month_end DATETIME, -- 本月末
@num int -- 开始结束天数
SET @today= '2050-01-01'
SELECT @month_bg= '1970-01-01' ; -- 本月初
set @num=datediff(day,@month_bg,@today) ;
-- 查询每日日期
with t as (
select convert(varchar(10),dateadd(month,t2.number,t1.day),120) date
from (select @month_bg day ) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=@num) t2 )
SELECT DATENAME(YEAR,date) 年,DATENAME(QUARTER,date) 季,DATENAME(MONTH,date) 月 FROM t