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

Question

考虑需要创建日期结果集。我们有开始和结束日期，我们想生成一个介于两者之间的日期列表。DECLARE  @Start datetime         ,@End  datetimeDECLARE @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 datetimeSELECT @dCounter = @StartWHILE @dCounter <= @EndBEGIN 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

    INTO Numbers

    FROM sys.objects s1

    CROSS JOIN sys.objects s2

ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置数字表后，请使用以下查询：

SELECT

    @Start+Number-1

    FROM Numbers

    WHERE Number<=DATEDIFF(day,@Start,@End)+1

捕捉它们：

DECLARE  @Start datetime

         ,@End  datetime

DECLARE @AllDates table

        (Date datetime)

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

INSERT INTO @AllDates

        (Date)

    SELECT

        @Start+Number-1

        FROM Numbers

        WHERE Number<=DATEDIFF(day,@Start,@End)+1

SELECT * FROM @AllDates

输出：

Date

-----------------------

2009-03-01 00:00:00.000

2009-03-02 00:00:00.000

2009-03-03 00:00:00.000

2009-03-04 00:00:00.000

2009-03-05 00:00:00.000

2009-03-06 00:00:00.000

2009-03-07 00:00:00.000

2009-03-08 00:00:00.000

2009-03-09 00:00:00.000

2009-03-10 00:00:00.000

....

2009-07-25 00:00:00.000

2009-07-26 00:00:00.000

2009-07-27 00:00:00.000

2009-07-28 00:00:00.000

2009-07-29 00:00:00.000

2009-07-30 00:00:00.000

2009-07-31 00:00:00.000

2009-08-01 00:00:00.000

(154 row(s) affected)