给出2个日期，有没有办法查出2个日期之间的所有日期？

[tjg5202]

例如：2009-9-28 2009-10-5

有什么办法能求出下面的日期
2009-9-28
2009-9-29
2009-9-30
2009-10-1
2009-10-2
2009-10-3
2009-10-4
2009-10-5

[好汉坡]

--2009-9-28  2009-10-5 

select date=dateadd(day,number,'2009-9-28')
from master..spt_values 
where type='p' and number>=0
and dateadd(day,number,'2009-9-28')<='2009-10-5'

date
-----------------------
2009-09-28 00:00:00.000
2009-09-29 00:00:00.000
2009-09-30 00:00:00.000
2009-10-01 00:00:00.000
2009-10-02 00:00:00.000
2009-10-03 00:00:00.000
2009-10-04 00:00:00.000
2009-10-05 00:00:00.000

(8 行受影响)

[--小F--]

declare @startdate datetime,@enddate datetime
set @startdate='2009-09-28'
set @enddate='2009-10-5'

select convert(varchar(10),dateadd(day,number,@startdate),120) 
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>0 
    and type='p'
/*----------
2009-09-29
2009-09-30
2009-10-01
2009-10-02
2009-10-03
2009-10-04
2009-10-05

(7 行受影响)
*/

[--小F--]

declare @startdate datetime,@enddate datetime
set @startdate='2009-09-28'
set @enddate='2009-10-5'

select convert(varchar(10),dateadd(day,number,@startdate),120) 
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>=0 
    and type='p'
/*----------
2009-09-28
2009-09-29
2009-09-30
2009-10-01
2009-10-02
2009-10-03
2009-10-04
2009-10-05

(8 行受影响)

*/

[tjg5202]

额。。好快

[黄_瓜]

declare @startdate datetime,@enddate datetime
set @startdate='2009-09-28'
set @enddate='2009-10-5'

select convert(varchar(10),dateadd(day,number,@startdate),120) 
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,@startdate), @enddate)>=0
    and number>=0 
    and type='p'
/*----------
2009-09-28
2009-09-29
2009-09-30
2009-10-01
2009-10-02
2009-10-03
2009-10-04
2009-10-05

(8 行受影响)

*/

[快乐_石头]

declare @dtstart datetime,@dtend datetime
select @dtstart='2009-9-28',@dtend='2009-10-5'
;with happy_stone(dtcol)
as
( select @dtstart dtcol
  union all
  select dateadd(day,1,dtcol) from happy_stone where dateadd(day,1,dtcol)<=@dtend
)
select * from happy_stone
/*
dtcol
-----------------------
2009-09-28 00:00:00.000
2009-09-29 00:00:00.000
2009-09-30 00:00:00.000
2009-10-01 00:00:00.000
2009-10-02 00:00:00.000
2009-10-03 00:00:00.000
2009-10-04 00:00:00.000
2009-10-05 00:00:00.000

(8 個資料列受到影響)


*/

[xman_78tom]

select dateadd(d,number,'2009-9-28') from master..spt_values 
where type='P' and number<=datediff(d,'2009-9-28','2009-10-5')

[tjg5202]

能否横向排列呢？？请教高手们

[icelovey]

学习

[xman_78tom]

declare @sql varchar(max)
set @sql='select '
select @sql=@sql+','''+convert(varchar(10),dateadd(day,number,'2009-9-28'),120)+''''
from master..spt_values where type='P' and number<=datediff(d,'2009-9-28','2009-10-5')
select @sql=stuff(@sql,8,1,' ')
--select @sql

exec(@sql)

[--小F--]

declare @sql varchar(max),@startdate datetime,@enddate datetime 
set @startdate='2009-09-28'
set @enddate='2009-10-5'
set @sql='select '
select @sql=@sql+','''+convert(varchar(10),dateadd(day,number,@startdate),120)+''''
from master..spt_values where type='P' and number<=datediff(dd,@startdate,@enddate)
select @sql=stuff(@sql,8,1,' ')
exec(@sql)
/*---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2009-09-28 2009-09-29 2009-09-30 2009-10-01 2009-10-02 2009-10-03 2009-10-04 2009-10-05

(1 行受影响)*/

[ADVANTECH_CYZ]

学习学习