在TSQL中生成递增日期的结果集

Question

考虑需要创建日期结果集。我们有开始和结束日期，我们想生成一个介于两者之间的日期列表。

DECLARE  @Start datetime

         ,@End  datetime

DECLARE @AllDates table

        (@Date datetime)

SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'

--need to fill @AllDates. Trying to avoid looping. 

-- Surely if a better solution exists.

考虑带有WHILE循环的当前实现：

DECLARE @dCounter datetime

SELECT @dCounter = @Start

WHILE @dCounter <= @End

BEGIN

 INSERT INTO @AllDates VALUES (@dCounter)

 SELECT @dCounter=@dCounter+1 

END

问题：如何使用T-SQL创建一组在用户定义范围内的日期？假设使用SQL 2005+。如果您的答案使用的是SQL 2008功能，请标记为此类。

Answer 1

为了使这种方法起作用，您需要执行以下时间表设置：SELECT TOP 10000 IDENTITY(int,1,1) AS Number&nbsp; &nbsp; INTO Numbers&nbsp; &nbsp; FROM sys.objects s1&nbsp; &nbsp; CROSS JOIN sys.objects s2ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)设置数字表后，请使用以下查询：SELECT&nbsp; &nbsp; @Start+Number-1&nbsp; &nbsp; FROM Numbers&nbsp; &nbsp; WHERE Number<=DATEDIFF(day,@Start,@End)+1捕捉它们：DECLARE&nbsp; @Start datetime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,@End&nbsp; datetimeDECLARE @AllDates table&nbsp; &nbsp; &nbsp; &nbsp; (Date datetime)SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'INSERT INTO @AllDates&nbsp; &nbsp; &nbsp; &nbsp; (Date)&nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; @Start+Number-1&nbsp; &nbsp; &nbsp; &nbsp; FROM Numbers&nbsp; &nbsp; &nbsp; &nbsp; WHERE Number<=DATEDIFF(day,@Start,@End)+1SELECT * FROM @AllDates输出：Date-----------------------2009-03-01 00:00:00.0002009-03-02 00:00:00.0002009-03-03 00:00:00.0002009-03-04 00:00:00.0002009-03-05 00:00:00.0002009-03-06 00:00:00.0002009-03-07 00:00:00.0002009-03-08 00:00:00.0002009-03-09 00:00:00.0002009-03-10 00:00:00.000....2009-07-25 00:00:00.0002009-07-26 00:00:00.0002009-07-27 00:00:00.0002009-07-28 00:00:00.0002009-07-29 00:00:00.0002009-07-30 00:00:00.0002009-07-31 00:00:00.0002009-08-01 00:00:00.000(154 row(s) affected)