--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 行受影响)
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 行受影响)
*/
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 @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 個資料列受到影響)
*/
select dateadd(d,number,'2009-9-28') from master..spt_values
where type='P' and number<=datediff(d,'2009-9-28','2009-10-5')
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)
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 行受影响)*/
