如何得到时间段内的所有日期

[stswordman]

给出起始时间和结束时间,然后的到之间的全部日期

例如其时日期为2007-1-1,结束日期为2007-1-5

得到如下结果

2007-1-1
2007-1-2
2007-1-3
2007-1-4
2007-1-5

[hrb2008]

create table #
(
col datetime
)
go
declare @begintime datetime
declare @endtime datetime
declare @day int
set @begintime=cast('2007-1-1' as datetime)
set @endtime =cast('2007-1-5' as datetime)
select @day=DATEDIFF(day, @begintime, @endtime)
while @day>=0
begin
insert into # select dateadd(day,@day,@begintime)
set @day=@day-1
end
select * from #
drop table #
---
col
-----------------------
2007-01-05 00:00:00.000
2007-01-04 00:00:00.000
2007-01-03 00:00:00.000
2007-01-02 00:00:00.000
2007-01-01 00:00:00.000

(5 行受影响)

[saiwong]

create t1 (date datetime)
declare @enddate as datetime
set @enddate='2007-1-1'
while @enddate<='2007-1-5'
begin
insert into t1 select @enddate
select @enddate=dateadd(day,1,@enddate)
end

[paoluo]

如果間隔大些的情況下，用循環的效率比較低，可以改用臨時表來實現。


Declare @StartDate DateTime, @EndDate DateTime
Select @StartDate = '2007-1-1' , @EndDate = '2007-1-5'
Select Top 100 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B
Select
DateAdd(dd, ID, @StartDate) As [Date]
From #T
Where ID <= DateDiff(dd, @StartDate, @EndDate)
Drop table #T
--Result
/*
Date
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
*/

[yahuu]

楼上的这个厉害，学习了

[yahuu]

Select Top 100 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B
这句后面的 Sysobjects B 做什么用？

[withcsharp]

declare @begin_t datetime
declare @end_t datetime
set @begin_t='1949-10-1'
set @end_t='2005-11-5'


declare @t table (d datetime)
declare @i int;
declare @c int;

insert into @t values(@begin_t)
set @c=@@rowcount
set @i=1
while @i>0
begin
insert into @t
select d+@c from @t
where d+@c <@end_t
set @i= @@rowcount
set @c= @c+@i
end

select * from @t