Declare @Dt DateTime Select @Dt = '2007-01-01' Select Top 1000 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B Select DateAdd(dd, ID, @Dt) As Dt From #T Where ID <= DateDiff(dd, @Dt, GetDate()) Drop Table #T
弄一个id表 从1-10000declare @dt datetime set @dt='2007-1-1'select top 10000 id=identity(int,0,1) into #t from sysobjects a,sysobjects bselect dateadd(day,id,@dt) from #t where dateadd(day,id,@dt)<getdate()
如果時間跨度更大的話,可以適當修改Select Top 1000 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B將其改為Select Top 5000 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B,或者更大.
如果你经常这么搞的话就建一个id表得了查询的时候直接declare @dt datetime set @dt='2007-1-1' select dateadd(day,id,@dt) from id表 where dateadd(day,id,@dt)<getdate()
create table # ( col datetime ) declare @d datetime,@day int set @d='2007-03-01' select @day=datediff(day,@d,getdate()) while @day>=0 begin insert into # select dateadd(day,@day,@d) set @day=@day-1 end select * from # drop table # -- col ----------------------- 2007-03-16 00:00:00.000 2007-03-15 00:00:00.000 2007-03-14 00:00:00.000 2007-03-13 00:00:00.000 2007-03-12 00:00:00.000 2007-03-11 00:00:00.000 2007-03-10 00:00:00.000 2007-03-09 00:00:00.000 2007-03-08 00:00:00.000 2007-03-07 00:00:00.000 2007-03-06 00:00:00.000 2007-03-05 00:00:00.000 2007-03-04 00:00:00.000 2007-03-03 00:00:00.000 2007-03-02 00:00:00.000 2007-03-01 00:00:00.000(16 行受影响)
declare @SDate datetime declare @i int select @sDate='2007-01-16' select @i=1 While @i<Datediff(d,@Sdate,getdate()) Begin print Dateadd(d,@i,@sdate) select @i=@i+1 end
Select @Dt = '2007-01-01'
Select Top 1000 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B
Select DateAdd(dd, ID, @Dt) As Dt From #T Where ID <= DateDiff(dd, @Dt, GetDate())
Drop Table #T
从1-10000declare @dt datetime
set @dt='2007-1-1'select top 10000 id=identity(int,0,1) into #t from sysobjects a,sysobjects bselect dateadd(day,id,@dt) from #t where dateadd(day,id,@dt)<getdate()
set @dt='2007-1-1'
select dateadd(day,id,@dt) from id表 where dateadd(day,id,@dt)<getdate()
(
col datetime
)
declare @d datetime,@day int
set @d='2007-03-01'
select @day=datediff(day,@d,getdate())
while @day>=0
begin
insert into # select dateadd(day,@day,@d)
set @day=@day-1
end
select * from #
drop table #
--
col
-----------------------
2007-03-16 00:00:00.000
2007-03-15 00:00:00.000
2007-03-14 00:00:00.000
2007-03-13 00:00:00.000
2007-03-12 00:00:00.000
2007-03-11 00:00:00.000
2007-03-10 00:00:00.000
2007-03-09 00:00:00.000
2007-03-08 00:00:00.000
2007-03-07 00:00:00.000
2007-03-06 00:00:00.000
2007-03-05 00:00:00.000
2007-03-04 00:00:00.000
2007-03-03 00:00:00.000
2007-03-02 00:00:00.000
2007-03-01 00:00:00.000(16 行受影响)
declare @i int
select @sDate='2007-01-16'
select @i=1
While @i<Datediff(d,@Sdate,getdate())
Begin print Dateadd(d,@i,@sdate)
select @i=@i+1
end