怎么得到会计期间日期列表

2013-07-09

如何得到会计期间日期列表比如给你一个参数2008和26就能得到三列数据2008012007-12-262008-01-25200802200

如何得到会计期间日期列表
比如给你一个参数  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

select * from @ta

[解决办法]
--借花献佛：
declare @year int,@day int,@date_b datetime
select @year=2008,@day=31
set @date_b=dateadd(month,-1,convert(datetime,convert(varchar(20), @year*10000+100+@day)))

select number
,dateadd(month,number-1,@date_b) startDate
,dateadd(day,-1,dateadd(month,number,@date_b)) endDate
from master..spt_values sv
where [type]='p' and number between 1 and 12

select number
,t.yearnumber*100+number as MonthNumber
,convert(datetime,convert(varchar(20), t.yearnumber*10000+number*100+1)) startDate
,dateadd(d,-1,dateadd(month,1,convert(datetime,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 between 1 and 12

热点排行

SQL Server

怎么得到会计期间日期列表