列出一段时间内所有的日期

number只有2048个数
select convert(varchar(10),dateadd(dd,number,convert(varchar(8),'2015-01-01',120)+'01'),120) as dt
from master..spt_values 
where type='P' 
and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(DAY,1222,getdate()),120)+'01')

 

 

原文：http://blog.csdn.net/fengqingtao2008/article/details/40298957

以下是比较精简的

SQL中给定某一日期段生成所有日期数据

SELECT DateAdd(day,number,'2014-09-29')
  FROM master..spt_values
 WHERE type = 'p'
   AND number <= DateDiff(day,'2014-09-29',GetDate())
需要convert的话

SELECT convert(varchar(10),DateAdd(day,number,'2015-01-01'),120) as dt into #temp
  FROM master..spt_values  
 WHERE type = 'p'  
   AND number <= convert(varchar(10),DateDiff(day,'2015-01-01',GetDate()))

   select * from #temp
   drop table #temp

 

 

2014-09-29 00:00:00.000
2014-09-30 00:00:00.000
2014-10-01 00:00:00.000
2014-10-02 00:00:00.000
2014-10-03 00:00:00.000
2014-10-04 00:00:00.000
2014-10-05 00:00:00.000
2014-10-06 00:00:00.000
2014-10-07 00:00:00.000
2014-10-08 00:00:00.000
2014-10-09 00:00:00.000
2014-10-10 00:00:00.000
2014-10-11 00:00:00.000
2014-10-12 00:00:00.000
2014-10-13 00:00:00.000
2014-10-14 00:00:00.000

 

改进

create table #temp (dt varchar(max))
declare @i int 

set @i = 1
while(@i <= 18000) begin --数字随意
insert into  #temp
SELECT convert(varchar(10),DateAdd(day,@i,'2011-01-01'),120) as dt 
where 1=1
   AND @i <= convert(varchar(10),DateDiff(day,'2011-01-01',GetDate())) --必定小于今天
set @i = @i + 1
end
   select * from #temp
   drop table #temp

 

参数化

CREATE TABLE #temp (dt VARCHAR(MAX))
DECLARE @i INT 
DECLARE @validfrom DATETIME
DECLARE @validThru DATETIME
SET @validfrom='2015-1-1'
SET @validThru='2418-12-31'
SET @i = 0

WHILE(@i <= DateDiff(day,@validfrom,@validThru)) 
BEGIN
    INSERT INTO  #temp
    SELECT CONVERT(VARCHAR(10),DATEADD(DAY,@i,'2015-1-1'),120) AS dt 
    WHERE @i <= CONVERT(VARCHAR(10),DATEDIFF(DAY,@validfrom,@validThru))
    SET @i = @i + 1
END

SELECT * FROM #temp
DROP TABLE #temp

 

2015-01-01
2015-01-02
2015-01-03
2015-01-04
2015-01-05
2015-01-06
2015-01-07
2015-01-08
2015-01-09
2015-01-10
2015-01-11
2015-01-12
2015-01-13
2015-01-14
2015-01-15
2015-01-16
2015-01-17
...
2018-12-31