使用T-SQL生成一个递增日期的结果集。

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 Server 2005或更高版本。如果您的答案使用了SQL Server 2008的功能，请标明。

- p.campbell

---

你使用的是哪个版本的MSSQL Server？如果我们给出了2008的示例，而你使用的是2000，那么谈论2008选项就毫无意义了。 - James Black

谢谢詹姆斯。我以为在问题中已经说明了“假设2005+”。 - p.campbell

Answer 1

如果您的日期相差不超过2047天：

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

select dateadd(day, number, @dt)
from 
    (select number from master.dbo.spt_values
     where [type] = 'P'
    ) n
where dateadd(day, number, @dt) < @dtEnd

在多次要求下，我更新了我的答案。为什么呢？

原始答案包含了子查询。

 select distinct number from master.dbo.spt_values
     where name is null

我在SQL Server 2008、2012和2016上测试后发现，这些方法都可以得到相同的结果。

然而，当我尝试分析MSSQL从 spt_values 查询时内部的代码时，我发现 SELECT 语句总是包含子句 WHERE [type]='[magic code]'。

因此，我认为虽然查询返回了正确的结果，但它是基于错误的原因得出的：

未来版本的SQL Server可能定义了不同的 [type] 值，该值也具有 [name] 的 NULL 值，超出了0-2047的范围，甚至是不连续的，这种情况下结果将是错误的。

- devio

---

在数据库服务器（Sybase ASE）中使用master.dbo.spt_values表会对性能产生影响吗？ - inlokesh

你是对的。我指的是由于大量重复的名称而产生的重复行...然后通过“DISTINCT”强制使它们唯一。当使用单个向量时，没有重复项，因此不需要“DISTINCT”。正如你在更新的答案中发现的那样。 - PerformanceDBA

对于最多 20482048 个数字，请使用两个向量：第一个向量按照答案；第二个向量为数字2048。 - PerformanceDBA

（1）Sybase没有递归的任何限制。 （2）这不是递归，而是投影。 （3）WHERE 0..1035将获取1036行，WHERE 1..99将获取99行。 - PerformanceDBA

@inlokesh。除非您有残疾的主数据库，否则不会影响性能。 spt代表系统过程表，type标识表或向量。 所有系统过程都使用spt_values，因此它保证被缓存。 - PerformanceDBA

Answer 2

以下是使用递归公共表达式(递归CTE，SQL Server 2005+)的示例：

WITH dates AS (
     SELECT CAST('2009-01-01' AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT ...
  FROM TABLE t
  JOIN dates d ON d.date = t.date --etc.

- OMG Ponies

---

递归不就是另一种写循环的方式吗？ - automatic

如果由于SQL Server 2000或更早版本的限制而无法使用CTE，则可以使用Numbers表。 - KM.

如果您尝试使用超过100天，您将会收到一个错误提示：“语句终止。在语句完成之前已经耗尽了最大递归次数100。” - gregmac

@gregmac：在定义CTE时，您可以设置MAXRECURSION提示。然而，如果您需要创建100多个日期，我认为递归创建可能不如其他替代方案扩展性好。 - OMG Ponies

这段代码可以稍作优化；为了避免两次调用DATEADD函数，可以将其放在FROM之后的CROSS APPLY中，然后在WHERE和SELECT子句中引用它。例如：CROSS APPLY ( SELECT DATEADD(dd, 1, t.date) [Value] ) [My Added Date Reference]。 - Matt Arnold

Answer 3

@KM的答案首先创建一个数字表，然后使用它来选择一段日期范围。如果不使用临时数字表进行相同操作，则可以采用以下方法：

DECLARE  @Start datetime
         ,@End  datetime
DECLARE @AllDates table
        (Date datetime)

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

WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
     Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
     Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
     Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    SELECT @Start+n-1 as Date
        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
            FROM Nbrs ) D ( n )
    WHERE n <= DATEDIFF(day,@Start,@End)+1 ;

测试一下，如果您经常这样做，永久表可能更具性能。

上面的查询是从这篇文章中修改而来，该文章讨论了生成序列并提供了许多可能的方法。我喜欢这个方法，因为它不创建临时表，并且不受sys.objects表中元素数量的限制。

- Chadwick

---

文章链接已失效。 - DaFi4

谢谢。我使用这个来为MS SQL Server创建一个日历表，并在这里发布了脚本：https://gist.github.com/randyburden/7e86e8c73c97a5482cdf6d803c0312db - Randy Burden

Answer 4

这个解决方案基于同一问题的MySQL精彩答案。它在MSSQL上也非常高效。https://dev59.com/tXI95IYBdhLWcg3wyBCc#2157776

select DateGenerator.DateValue from (
  select DATEADD(day, - (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)), CONVERT(DATE, GETDATE()) ) as DateValue
  from (select a.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a
  cross join (select b.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b
  cross join (select c.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c
  cross join (select d.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(a)) as d
) DateGenerator
WHERE DateGenerator.DateValue BETWEEN 'Mar 1 2009' AND 'Aug 1 2009'
ORDER BY DateGenerator.DateValue ASC

该查询仅适用于过去的日期，如果要查询未来日期，请将DATEADD函数中的减号更改为加号。该查询仅适用于SQL Server 2008及以上版本，但可以通过将"select from values"结构替换为union来重写2005版本。

- Marek Gregor

Answer 5

为了让这种方法起作用，您需要进行一次表格设置：

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)

- KM.

---

@KM：使用这种方法与递归CTE相比，有何优势？ - OMG Ponies

如果您要进行一次性的表格设置，为什么不建立一个一次性的表格设置，其中包含从1900-01-01到2099-12-31的所有日期？ - automatic

@automatic，数字表对于很多事情都非常有用：http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html，并且使日期表变得不必要。 - KM.

@KM：你怎么知道笛卡尔积至少会生成10000条记录？ - eKek0

@eKek0，你只需要将100行自身交叉连接即可生成10,000条记录，如果不够的话可以尝试使用sys.columns。 - KM.

@Rexem，一个优点是它可以在2005年之前的版本中运行，因为那时还没有CTE。 - KM.

Answer 6

尝试这个方法。不需要循环、CTE限制等，你可以生成任意数量的记录。根据需要管理交叉连接和顶部。

select top 100000 dateadd(d,incr,'2010-04-01') as dt from
(select  incr = row_number() over (order by object_id, column_id), * from
(
select a.object_id, a.column_id from  sys.all_columns a cross join sys.all_columns b
) as a
) as b

请注意，嵌套是为了更方便地控制和转换为视图等。

- Kapil

---

这与K.M.的答案有何不同？ - ypercubeᵀᴹ

A. 它使用现有的表 - sys.all_columns 总是有记录，表和列的数量越多，这里的记录就越多。仅使用20个表，我在这个表中有超过5000行，并通过交叉连接得到了5000^2行。 B. 没有声明，没有循环，非常快速可靠。 C. 只需为内部SQL创建一个名为numbergenerator的视图，并在需要时使用前n行 - 非常可重用。 坦白地说，我自己已经使用这个方法很长时间了。 - Kapil

Answer 7

我使用以下内容：

SELECT * FROM dbo.RangeDate(GETDATE(), DATEADD(d, 365, GETDATE()));

-- Generate a range of up to 65,536 contiguous DATES
CREATE FUNCTION dbo.RangeDate (   
    @date1 DATE = NULL
  , @date2 DATE = NULL
)   
RETURNS TABLE   
AS   
RETURN (
    SELECT D = DATEADD(d, A.N, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
    FROM dbo.RangeSmallInt(0, ABS(DATEDIFF(d, @date1, @date2))) A
);

-- Generate a range of up to 65,536 contiguous BIGINTS
CREATE FUNCTION dbo.RangeSmallInt (
    @num1 BIGINT = NULL
  , @num2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
    WITH Numbers(N) AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )    
    SELECT TOP (
               CASE
                   WHEN @num1 IS NOT NULL AND @num2 IS NOT NULL THEN ABS(@num1 - @num2) + 1
                   ELSE 0
               END
           )
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
    FROM Numbers A
       , Numbers B
    WHERE ABS(@num1 - @num2) + 1 < 65537
);

这个解决方案并不比已经提出的许多解决方案有很大的区别，但是有几个我喜欢的地方：

• 不需要表格
• 参数可以以任何顺序传递
• 65536个日期的限制是任意的，可以通过切换到RangeInt等函数轻松扩展

- Kittoes0124

Answer 8

我喜欢CTE，因为它易于阅读和维护

Declare @mod_date_from date =getdate();
Declare @mod_date_to date =dateadd(year,1,@mod_date_from);

with cte_Dates as (
            SELECT @mod_date_from as reqDate
            UNION ALL
            SELECT DATEADD(DAY,1,reqDate)
            FROM cte_Dates
            WHERE DATEADD(DAY,1,reqDate) < @mod_date_to
        )
        SELECT * FROM cte_Dates
        OPTION(MAXRECURSION 0);

不要忘记设置 MAXRECURSION

- Shahab J

Answer 9

另一种选择是在.NET中创建相应的函数。以下是其示例：

[Microsoft.SqlServer.Server.SqlFunction(
  DataAccess = DataAccessKind.None,
  FillRowMethodName = "fnUtlGetDateRangeInTable_FillRow",
  IsDeterministic = true,
  IsPrecise = true,
  SystemDataAccess = SystemDataAccessKind.None,
  TableDefinition = "d datetime")]
public static IEnumerable fnUtlGetDateRangeInTable(SqlDateTime startDate, SqlDateTime endDate)
{
    // Check if arguments are valid

    int numdays = Math.Min(endDate.Value.Subtract(startDate.Value).Days,366);
    List<DateTime> res = new List<DateTime>();
    for (int i = 0; i <= numdays; i++)
        res.Add(dtStart.Value.AddDays(i));

    return res;
}

public static void fnUtlGetDateRangeInTable_FillRow(Object row, out SqlDateTime d)
{
    d = (DateTime)row;
}

这基本上是一个原型，可以变得更加智能化，但是它说明了这个想法。根据我的经验，在小到中等时间跨度（比如几年）内，这个函数的表现比T-SQL实现的要好。CLR版本的另一个好处是它不会创建临时表。

- AlexS

Answer 10

概述

这是我的版本（兼容2005）。 这种方法的优点是：

• 您可以获得一个通用函数，可用于许多类似的场景； 不仅限于日期
• 范围不受现有表格内容的限制
• 您可以轻松更改增量（例如每7天获取一次日期而不是每天）
• 您不需要访问其他目录（即主目录）
• SQL引擎能够对TVF进行一些优化，而无法使用while语句
• generate_series在其他数据库中也使用，因此这可能有助于使您的代码对更广泛的受众具有直观的熟悉感

SQL Fiddle：http://sqlfiddle.com/！6/c3896/1

代码

根据给定参数生成数字范围的可重复使用函数：

create function dbo.generate_series
(
      @start bigint
    , @stop bigint
    , @step bigint = 1
    , @maxResults bigint = 0 --0=unlimitted
)
returns @results table(n bigint)
as
begin

    --avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
    if @step = 0 return
    if @start > @stop and @step > 0 return
    if @start < @stop and @step < 0 return

    --ensure we don't overshoot
    set @stop = @stop - @step

    --treat negatives as unlimited
    set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

    --generate output
    ;with myCTE (n,i) as 
    (
        --start at the beginning
        select @start
        , 1
        union all
        --increment in steps
        select n + @step
        , i + 1
        from myCTE 
        --ensure we've not overshot (accounting for direction of step)
        where (@maxResults=0 or i<@maxResults)
        and 
        (
               (@step > 0 and n <= @stop)
            or (@step < 0 and n >= @stop)
        )  
    )
    insert @results
    select n 
    from myCTE
    option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

    --all good  
    return

end

将此应用于您的情况：

declare @start datetime = '2013-12-05 09:00'
       ,@end  datetime = '2014-03-02 13:00'

--get dates (midnight)
--, rounding <12:00 down to 00:00 same day, >=12:00 to 00:00 next day
--, incrementing by 1 day
select CAST(n as datetime)
from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default)

--get dates (start time)
--, incrementing by 1 day
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, 24, default)

--get dates (start time)
--, incrementing by 1 hour
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, default, default)

2005兼容

• 通用表达式：http://technet.microsoft.com/zh-cn/library/ms190766(v=sql.90).aspx
• Option MaxRecursion提示：http://technet.microsoft.com/zh-cn/library/ms181714(v=sql.90).aspx
• 表值函数：http://technet.microsoft.com/zh-cn/library/ms191165(v=sql.90).aspx
• 默认参数：http://technet.microsoft.com/zh-cn/library/ms186755(v=sql.90).aspx
• 日期时间：http://technet.microsoft.com/zh-cn/library/ms187819(v=sql.90).aspx
• 类型转换：http://technet.microsoft.com/zh-cn/library/aa226054(v=sql.90).aspx

- JohnLBevan