TSQL: Generate a resultset of incrementing dates

Question

Consider the need to create a resultset of dates. We've got a 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 loop here if no better sol'n 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. Thank you!

Answer 1

What I'd recommend: create an auxiliary table of numbers and use it to generate your list of dates. You can also use a recursive CTE, but that may not perform as well as joining to an auxiliary table of numbers. See http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers for info on both options.

Answer 2

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 3

create a temp table with integers from 0 to the difference between your two dates.

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

Answer 4

While I really like KM's solution above (+1), I must question your "no loop" assumption - given the plausible date ranges that your app will work with, having a loop should not really be all that expensive. The main trick is to strore the results of the loop in staging/cache table, so that extremely large sets of queries do not slow down the system by re-calculating the same exact dates. E.g. each query only computes/caches the date ranges that are NOT already in cache and that it needs (and pre-populate the table with some realistic date range like ~2 years in advance, with range determined by your application business needs).

Answer 5

If your dates are no more than 2047 days apart:

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

select dateadd(day, number, @dt)
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd

Answer 6

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 7

@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 8

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.