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 行受影响)
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
如果間隔大些的情況下,用循環的效率比較低,可以改用臨時表來實現。 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 */
Select Top 100 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B 这句后面的 Sysobjects B 做什么用?
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 endselect * from @t
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 行受影响)
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
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
*/
这句后面的 Sysobjects B 做什么用?
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
endselect * from @t