Generate a resultset of incrementing dates in TSQL

Question

Consider the need to create a resultset of dates. We've got start and end dates, and we'd like to generate a list of dates in between.

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.

Consider the current implementation with a WHILE loop:

DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
 INSERT INTO @AllDates VALUES (@dCounter)
 SELECT @dCounter=@dCounter+1 
END

Question: How would you create a set of dates that are within a user-defined range using T-SQL? Assume SQL 2005+. If your answer is using SQL 2008 features, please mark as such.

Answer 1

For this method to work, you need to do this one time table setup:

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)

Once the Numbers table is set up, use this query:

SELECT
    @Start+Number-1
    FROM Numbers
    WHERE Number<=DATEDIFF(day,@Start,@End)+1

to capture them do:

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

output:

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 2

@KM's answer creates a numbers table first, and uses it to select a range of dates. To do the same without the temporary numbers table:

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 ;

Test of course, if you are doing this often, a permanent table may well be more performant.

The query above is a modified version from this article, which discusses generating sequences and gives many possible methods. I liked this one as it does not create a temp table, and is not limited to the number of elements in the sys.objects table.

Answer 3

Another option is to create corresponding function in .NET. Here's how it looks like:

[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;
}

This is basically a prototype and it can be made a lot smarter, but illustrates the idea. From my experience, for a small to moderate time spans (like a couple of years) this function performs better than the one implemented in T-SQL. Another nice feature of CLR version is that it does not creates temporary table.

Answer 4

Tthe following uses a recursive 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.

Answer 5

The best answer is probably to use the CTE, but there is no guarantee you are able to use that. In my case, I had to insert this list inside an existing query created dinamically by a query generator...couldn't use CTE nor stored procedures.

So, the answer from Devio was really useful, but I had to modify it to work in my environment.

In case you don't have access to the master db, you may use another table in your database. As for the example before, the maximum date range is given by the number of rows inside the table choosen.

In my example tough, using the row_number, you can use tables without an actual int column.

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 6

Overview

Here's my version (2005 compatible). The advantages of this approach are:

• you get a general purpose function which you can use for a number of similar scenarios; not restricted to just dates
• the range isn't limited by the contents of an existing table
• you can easily change the increment (e.g. get the date every 7 days instead of every day)
• you don't require access to other catalogs (i.e. master)
• the sql engine's able to do some optimisation of the TVF that it couldn't with a while statement
• generate_series is used in some other dbs, so this may help make your code instinctively familiar to a wider audience

SQL Fiddle: http://sqlfiddle.com/#!6/c3896/1

Code

A reusable function for generating a range of numbers based on given parameters:

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

Putting this to use for your scenario:

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 Compatible

• Common Table Expressions: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
• Option MaxRecursion Hint: http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
• Table Valued Functions: http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
• Default Parameters: http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
• DateTime: http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
• Casting: http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx