转载

[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)

*/

文章最后发布于: 2012-02-16 12:39:44
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 鲸 设计师: meimeiellie

分享到微信朋友圈

×

扫一扫,手机浏览