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

最新推荐文章于 2020-11-03 22:56:27 发布

weixin_33978016

最新推荐文章于 2020-11-03 22:56:27 发布

阅读量210

点赞数

文章标签： c#

本文介绍了一种在SQL Server中生成指定日期范围内所有日期的方法，提供了两种不同的函数实现方式，第一种使用递归CTE生成日期序列，第二种利用系统表`spt_values`快速填充日期。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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