【叶子函数分享四十九】生成两个时间之间的所有日期

--改写liangCK的部分代码为函数

--创建函数

create function generateTime

(

@begin_date datetime,

@end_date datetime

)

returns @t table(date datetime)

as

begin

with maco as

(

select @begin_date AS date

union all

select date+1 from maco

where date+1 <=@end_date

)

insert into @t

select * from maco option(maxrecursion 0);

return

end

go

--测试示例

select * from dbo.generateTime('2009-01-01','2009-01-10')

--运行结果

/*

date

-----------------------

2009-01-01 00:00:00.000

2009-01-02 00:00:00.000

2009-01-03 00:00:00.000

2009-01-04 00:00:00.000

2009-01-05 00:00:00.000

2009-01-06 00:00:00.000

2009-01-07 00:00:00.000

2009-01-08 00:00:00.000

2009-01-09 00:00:00.000

2009-01-10 00:00:00.000

*/

go

--第二版

--创建函数

create function generateTimeV2

(

@begin_date datetime,

@end_date datetime

)

returns @t table(date datetime)

as

begin

insert into @t

select dateadd(dd,number,@begin_date) AS date

from master..spt_values

where type='p' and dateadd(dd,number,@begin_date)<=@end_date

return

end

--测试示例

select * from dbo.generateTimeV2('2009-01-01','2009-01-10')

--运行结果

/*

date

-----------------------

2009-01-01 00:00:00.000

2009-01-02 00:00:00.000

2009-01-03 00:00:00.000

2009-01-04 00:00:00.000

2009-01-05 00:00:00.000

2009-01-06 00:00:00.000

2009-01-07 00:00:00.000

2009-01-08 00:00:00.000

2009-01-09 00:00:00.000

2009-01-10 00:00:00.000

(10 row(s) affected)

*/