生成TSQL递增日期的一个结果(Generate a resultset of incrementi

考虑需要创建日期的一个结果。 我们有开始和结束日期，我们希望产生之间的日期列表。

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:

如果您的日期没有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]值也具有NULL作为值[name] ，的0-2047的范围外，或甚至不连续的，在这种情况下，结果将是简单地错误。

Answer 2:

里边反以下使用递归CTE（SQL服务器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.

Answer 3:

对于这种方法的工作，你需要做的这一次表设置：

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)

一旦Numbers表格设置，使用此查询：

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)

Answer 4:

@ 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的表。

Answer 5:

试试这个。 无循环，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

请注意，嵌套是比较容易控制和转换成视图等

Answer 6:

另一种选择是在.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版本的另一个优点是，它不创建临时表。

Answer 7:

概观

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

• 你得到它，你可以使用一些类似场景的通用功能; 不再仅仅限于日期
• 的范围不是由现有的表的内容的限制
• 你可以很容易地改变增量（例如获取日期，每7天，而不是每一天）
• 您不需要访问其他目录（即主）
• SQL引擎的能够做的TVF的一些优化，它不能用while语句
• generate_series在其他一些DBS使用，所以这可能有助于使你的代码本能地熟悉到更广泛的受众

SQL小提琴： 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/en-us/library/ms190766(v=sql.90).aspx
• 选项MAXRECURSION提示： http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
• 表值函数： http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
• 默认参数： http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
• 日期时间： http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
• 铸造： http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx

Answer 8:

该解决方案是基于MySQL的同一个问题的答案奇妙。 它也是非常MSSQL高性能。 https://stackoverflow.com/a/2157776/466677

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+，但可以通过更换也改写了2005年“从价值选择”与工会建设。

Answer 9:

创建一个从0到你的两个日期之间的差值整数临时表。

SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;

Answer 10:

我用的是以下几点：

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扩大

Answer 11:

我喜欢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

Answer 12:

我建议你：创建一个数字的辅助表，并用它来生成日期列表。 您还可以使用递归CTE，但可能不执行以及加入到数字的辅助表。 请参阅SQL，数字辅助表上这两个选项的信息。

Answer 13:

虽然我很喜欢上面的（+1）KM的解决方案，我必须质疑你的“无循环”的假设 - 定的合理的日期范围，您的应用程序将有工作，有一个循环真不该是最昂贵的。 主要的窍门是在strore分期/缓存表中循环的结果，这样非常大集的查询的不通过重新计算相同的确切日期拖慢系统。 例如，每个查询只计算/缓存已经不在缓存中的日期范围，它需要（与一些现实的日期范围预填充表一样〜2年提前，与您的应用程序的业务需求确定范围内）。

Answer 14:

最好的答案可能是使用热膨胀系数，但也不能保证你能够使用它。 就我而言，我不得不插入此列表中查询产生dinamically创建的现有查询中......不能使用CTE也不存储过程。

所以，从Devio答案是真正有用的，但我不得不改变它在我的环境中工作。

如果您没有访问到主数据库，您可以在数据库中使用另一个表。 至于之前的例子中，最大的日期范围由表choosen内的行的数目给出。

在我的例子强悍，使用ROW_NUMBER，您可以使用表格没有实际INT列。

declare @bd datetime --begin date
declare @ed datetime --end date

set @bd = GETDATE()-50
set @ed = GETDATE()+5

select 
DATEADD(dd, 0, DATEDIFF(dd, 0, Data)) --date format without time
from 
(
    select 
    (GETDATE()- DATEDIFF(dd,@bd,GETDATE())) --Filter on the begin date
    -1 + ROW_NUMBER() over (ORDER BY [here_a_field]) AS Data 
    from [Table_With_Lot_Of_Rows]
) a 
where Data < (@ed + 1) --filter on the end date

Answer 15:

我真的很喜欢Devio的解决方案，我需要的正是这样的事情，需要SQL Server 2000上运行的（所以不能用CTE），但是，怎么会被修改，只产生与一组给定的星期几的对齐日期。 例如，我只想落于周一，周三和周五或任何特定的顺序，我选择基于以下数计划行日期：

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

例：

StartDate = '2015-04-22' EndDate = '2017-04-22' --2 years worth
Filter on: 2,4,6 --Monday, Wednesday, Friday dates only

我试图代码是增加两个附加字段：一天，day_code然后用条件过滤生成的列表...

我想出了以下内容：

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

select dateadd(day, number, @dt) as Date, DATENAME(DW, dateadd(day, number, @dt)) as Day_Name into #generated_dates
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd 

select * from #generated_dates where Day_Name in ('Saturday', 'Friday')

drop table #generated_dates

Answer 16:

这一次应该工作。

选择系统对象前1000 DATEADD（d，ROW_NUMBER（）OVER（ORDER BY编号），GETDATE（））

文章来源: Generate a resultset of incrementing dates in TSQL