获取两个时间之内的所有日期 --sql 2000 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) dt 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 --sql 2005 declare @startDate datetime declare @endDate datetimeSELECT @startDate = '2008-10-15' ,@endDate = '2009-01-23' ;WITH tb AS ( SELECT @startDate AS 'date' UNION ALL SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate ) SELECT tb.date from tb
dateadd(dd,number,'2010-09-15')
from
master..spt_values
where
type='p'
and
dateadd(dd,number,'2010-09-15')<='2010-10-15'
declare @t table(s datetime)
declare @s datetime,@s1 datetime
set @s = '2010-09-15'
set @s1 = '2010-10-15'
while @s <= @s1
begin
insert into @t select @s
set @s = dateadd(dd,1,@s)
end
select * from @t结果
S
2010-09-15 00:00:00.000
2010-09-16 00:00:00.000
2010-09-17 00:00:00.000
2010-09-18 00:00:00.000
2010-09-19 00:00:00.000
2010-09-20 00:00:00.000
2010-09-21 00:00:00.000
2010-09-22 00:00:00.000
2010-09-23 00:00:00.000
2010-09-24 00:00:00.000
2010-09-25 00:00:00.000
2010-09-26 00:00:00.000
2010-09-27 00:00:00.000
2010-09-28 00:00:00.000
2010-09-29 00:00:00.000
2010-09-30 00:00:00.000
2010-10-01 00:00:00.000
2010-10-02 00:00:00.000
2010-10-03 00:00:00.000
2010-10-04 00:00:00.000
2010-10-05 00:00:00.000
2010-10-06 00:00:00.000
2010-10-07 00:00:00.000
2010-10-08 00:00:00.000
2010-10-09 00:00:00.000
2010-10-10 00:00:00.000
2010-10-11 00:00:00.000
2010-10-12 00:00:00.000
2010-10-13 00:00:00.000
2010-10-14 00:00:00.000
2010-10-15 00:00:00.000
--sql 2000
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) dt
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
--sql 2005
declare @startDate datetime
declare @endDate datetimeSELECT @startDate = '2008-10-15' ,@endDate = '2009-01-23'
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate
)
SELECT tb.date from tb