[MSSQL]生成两个时间之间的所有日期

--改写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) */