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

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

*/