比如我想查看2007-1-1到2007-6-6包含的所有日期
------解决方案--------------------
select top 1000 id=identity(int ,1,1) into #a from sysobjects a ,sysobjects b
select dateadd(dd,id, '2007-1-1 '),id from #a where dateadd(dd,id, '2007-1-1 ') <= '2007-6-6 '
------解决方案--------------------
set nocount on
declare @t1 datetime
declare @t2 datetime
declare @i int
set @t1= '2007-1-1 '
set @t2= '2007-6-6 '
declare @tbl table(t datetime)
set @i=0
while @i <=datediff(day,@t1,@t2)
begin
insert into @tbl select dateadd(day,@i,@t1)
set @[email protected]+1
end
select * from @tbl
------解决方案--------------------
declare @dtBegin datetime
declare @dtEnd datetime
declare @t table(dt datetime)
set datefirst 1
set @dtBegin= '2007-1-1 '
set @dtEnd= '2007-6-1 '
while @dtBegin <@dtEnd
begin
if datediff(day,@dtBegin,@dtEnd) <> 0
begin
insert @t select @dtBegin
end
set @[email protected]+1
end
select * from @t
------解决方案--------------------
declare @d1 datetime
declare @d2 datetime
declare @s varchar(1000)
declare @i int
declare @f int
select
@s = ' ',
@f = 0,
@d1 = '2007-1-1 ',
@d2 = '2007-6-6,
@i = datediff(day,@d1,@d2)
while @f <= @i
begin
select @s = @s + 'select ' ' '
+ convert(varchar(10),dateadd(day,@f,@d1),120)
+ ' ' ' union all '
select @f = @f + 1
end
select @s = substring(@s,1,len(@s)-10)
exec(@s)
------解决方案--------------------
declare @t table(id int identity(1, 1), dt datetime)
declare @d datetime
select @d = cast( '2007-1-1 ' as datetime)
while @d <= '2007-6-6 '
begin
insert @t select @d
set @d = dateadd(day, 1, @d)
end
select dt from @t
------解决方案--------------------
select top 800 id=identity(int ,0,1) into #a from syscolumns
select convert(varchar(10), dateadd(dd,id, '2007-1-1 '),120),id from #a where dateadd(dd,id, '2007-1-1 ') <= '2007-6-6 '
drop table #a
------解决方案--------------------
step 1
--------------------
create table #date(
riqi datetime
)
-------------------
step 2
------------------
declare @start_day datetime,@end_day datetime,@add_day datetime
set @start_day = '2007-01-01 '
set @end_day = '2007-06-06 '
while @start_day <= @end_day
begin
insert into #date select @start_day
select @start_day = dateadd(day,1,@start_day)
end
---------------------------------------------------
step 3
---------------------------------------------------
select * from #date
------解决方案--------------------
declare @table table(ddd datetime)
declare @stardate datetime, @enddate datetime
set @stardate = '2007-1-1 '
set @enddate = '2007-6-6 '
insert into @table select @stardate