比如给你一个参数 2008和26
就能得到三列数据
200801 2007-12-26 2008-01-25
200802 2008-01-26 2008-02-25
-------
200812 2008-11-26 2008-12-25
还有一个是自然月
给个2008
200801 2008-01-01 2008-01-31
200802 2008-02-01 2008-02-30
-------
200812 2008-12-01 2008-12-30
------解决方案--------------------
declare @year int =2008
declare @day int=26;
select number
,t.yearnumber*100+number as MonthNumber
,dateadd(d,1,dateadd(month,-1,convert(date,convert(varchar(20), t.yearnumber*10000+number*100+daynumber-1)))) startDate
,convert(date,convert(varchar(20), t.yearnumber*10000+number*100+daynumber-1)) endDate
from master..spt_values sv
cross join (select @year yearnumber,@day daynumber) as t
where [type]='p'
and number>0
and number<=12
select number
,t.yearnumber*100+number as MonthNumber
,convert(date,convert(varchar(20), t.yearnumber*10000+number*100+1)) startDate
,dateadd(d,-1,dateadd(month,1,convert(date,convert(varchar(20), t.yearnumber*10000+number*100+1)))) endDate
from master..spt_values sv
cross join (select @year yearnumber,@day daynumber) as t
where [type]='p'
and number>0
and number<=12
------解决方案--------------------
还有一个是自然月
给个2008
declare @c char(4),@i int
select @c='2008',@i=1
declare @ta table(ym char(6),date_b datetime,date_e datetime)
while @i<=12
begin
insert @ta (ym,date_b)
select @c+replicate('0',2-len(rtrim(@i)))+rtrim(@i),
@c+'-'+replicate('0',2-len(rtrim(@i)))+rtrim(@i)+'-01'
set @i=@i+1
end
update @ta set date_e=isnull(dateadd(day,-1,b.date_b),@c+'-12-31')
from @ta a
left join (select * from @ta) b on dateadd(month,1,a.date_b)=b.date_b