求一个遍历起始日期到结束日期之间所有日期的函数

[微醺_zZ]

求一个遍历起始日期到结束日期之间所有日期的函数，请各位高手帮帮忙

[dawugui]

--获取两个时间之内的所有日期
declare @sdate datetime
declare @edate datetime
set @sdate = '2008-10-15 00:00:000'
set @edate = '2009-02-10 00:00:000'


select 
    dateadd(dd,num,@sdate) 
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate

/*
                                                       
------------------------------------------------------ 
2008-10-15 00:00:00.000
2008-10-16 00:00:00.000
2008-10-17 00:00:00.000
2008-10-18 00:00:00.000
2008-10-19 00:00:00.000
2008-10-20 00:00:00.000
2008-10-21 00:00:00.000
2008-10-22 00:00:00.000
2008-10-23 00:00:00.000
2008-10-24 00:00:00.000
2008-10-25 00:00:00.000
2008-10-26 00:00:00.000
2008-10-27 00:00:00.000
2008-10-28 00:00:00.000
2008-10-29 00:00:00.000
2008-10-30 00:00:00.000
2008-10-31 00:00:00.000
2008-11-01 00:00:00.000
2008-11-02 00:00:00.000
2008-11-06 00:00:00.000
2008-11-10 00:00:00.000
2008-11-12 00:00:00.000
2008-11-13 00:00:00.000
2008-11-18 00:00:00.000
2008-11-20 00:00:00.000
2008-11-21 00:00:00.000
2008-11-22 00:00:00.000
2008-11-24 00:00:00.000
2008-11-25 00:00:00.000
2008-11-26 00:00:00.000
2008-11-28 00:00:00.000
2008-11-29 00:00:00.000
2008-11-30 00:00:00.000
2008-12-02 00:00:00.000
2008-12-04 00:00:00.000
2008-12-06 00:00:00.000
2008-12-08 00:00:00.000
2008-12-09 00:00:00.000
2008-12-10 00:00:00.000
2008-12-12 00:00:00.000
2008-12-14 00:00:00.000
2008-12-15 00:00:00.000
2008-12-16 00:00:00.000
2008-12-18 00:00:00.000
2008-12-20 00:00:00.000
2008-12-22 00:00:00.000
2008-12-24 00:00:00.000
2008-12-26 00:00:00.000
2008-12-28 00:00:00.000
2008-12-30 00:00:00.000
2009-01-01 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
2009-01-11 00:00:00.000
2009-01-12 00:00:00.000
2009-01-13 00:00:00.000
2009-01-14 00:00:00.000
2009-01-15 00:00:00.000
2009-01-16 00:00:00.000
2009-01-17 00:00:00.000
2009-01-22 00:00:00.000
2009-01-26 00:00:00.000
2008-11-07 00:00:00.000
2008-11-08 00:00:00.000
2008-11-09 00:00:00.000
2008-11-11 00:00:00.000
2008-11-14 00:00:00.000
2008-11-16 00:00:00.000
2008-11-15 00:00:00.000
2008-11-17 00:00:00.000
2008-11-19 00:00:00.000
2008-11-23 00:00:00.000
2008-11-27 00:00:00.000
2008-12-01 00:00:00.000
2008-12-03 00:00:00.000
2008-12-05 00:00:00.000
2008-12-07 00:00:00.000
2008-12-11 00:00:00.000
2008-12-13 00:00:00.000
2008-12-17 00:00:00.000
2008-12-19 00:00:00.000
2008-12-21 00:00:00.000
2008-12-23 00:00:00.000
2008-12-25 00:00:00.000
2008-12-27 00:00:00.000
2008-12-29 00:00:00.000
2008-12-31 00:00:00.000
2009-01-02 00:00:00.000
2009-01-18 00:00:00.000
2009-01-19 00:00:00.000
2009-01-20 00:00:00.000
2009-01-21 00:00:00.000
2009-01-23 00:00:00.000
2009-01-24 00:00:00.000
2009-01-25 00:00:00.000
2008-11-03 00:00:00.000
2008-11-04 00:00:00.000
2008-11-05 00:00:00.000
2009-01-27 00:00:00.000

（所影响的行数为 105 行）

*/

[ChinaJiaBing]

不明白...

[htl258_Tony]

create function getdatefn(@dt datetime,@et datetime)
returns @t table(a datetime)
as 
begin
insert @t select dateadd(dd,number,@dt) from master..spt_values where type='p' and number<=datediff(dd,@dt,@et)
return
end

select * from getdatefn('2008-05-01','2008-05-10')

a
-----------------------
2008-05-01 00:00:00.000
2008-05-02 00:00:00.000
2008-05-03 00:00:00.000
2008-05-04 00:00:00.000
2008-05-05 00:00:00.000
2008-05-06 00:00:00.000
2008-05-07 00:00:00.000
2008-05-08 00:00:00.000
2008-05-09 00:00:00.000
2008-05-10 00:00:00.000

(10 行受影响)

[htl258_Tony]

最好加上dbo,如下:

select * from dbo.getdatefn('2008-05-01','2008-05-10')