您的位置：程序门 -> ms-sql server -> 基础类

如何列出日期

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

如何列出日期

发表于：2007-03-16 09:38:56 楼主

怎样列出指定日期到当天的每一天？

发表于：2007-03-16 09:44:061楼得分:0

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

发表于：2007-03-16 09:44:572楼得分:0

弄一个id表
从1-10000

declare @dt datetime
set @dt= '2007-1-1 '

select top 10000 id=identity（int,0,1) into #t from sysobjects a,sysobjects b

select dateadd（day,id,@dt) from #t where dateadd（day,id,@dt) <getdate（)

发表于：2007-03-16 09:45:043楼得分:0

如果時間跨度更大的話，可以適當修改

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

,或者更大.

发表于：2007-03-16 09:46:504楼得分:0

如果你经常这么搞的话就建一个id表得了

查询的时候直接

declare @dt datetime
set @dt= '2007-1-1 '
select dateadd（day,id,@dt) from id表 where dateadd（day,id,@dt) <getdate（)

发表于：2007-03-16 09:50:155楼得分:0

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 行受影响)

发表于：2007-03-16 10:53:426楼得分:0

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