19

Possible Duplicate:
Getting Dates between a range of dates

Let's say I have 2 dates (date part only, no time) and I want to get all dates between these 2 dates inclusive and insert them in a table. Is there an easy way to do it with a SQL statement (i.e without looping)?

Ex:
Date1: 2010-12-01
Date2: 2010-12-04

Table should have following dates:
2010-12-01, 2010-12-02, 2010-12-03, 2010-12-04
Community
  • 1
  • 1
dcp
  • 54,410
  • 22
  • 144
  • 164

4 Answers4

53

Assuming SQL Server 2005+, use a recursive query:

WITH sample AS (
  SELECT CAST('2010-12-01' AS DATETIME) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM sample s
   WHERE DATEADD(dd, 1, dt) <= CAST('2010-12-04' AS DATETIME))
SELECT * 
  FROM sample

Returns:

 dt
 ---------
 2010-12-01 00:00:00.000
 2010-12-02 00:00:00.000
 2010-12-03 00:00:00.000
 2010-12-04 00:00:00.000

Use CAST/CONVERT to format as you like.

Using parameters for start & end:

INSERT INTO dbo.YOUR_TABLE
  (datetime_column)
WITH sample AS (
    SELECT @start_date AS dt
    UNION ALL
    SELECT DATEADD(dd, 1, dt)
      FROM sample s
     WHERE DATEADD(dd, 1, dt) <= @end_date)
SELECT s.dt
  FROM sample s
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    See this answer for [performance benchmarks](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232) of recursive CTEs. – Martin Smith Oct 15 '10 at 21:17
  • @Martin Smith: Interesting - KM and I compared about a year back, found the subtreecost for the example put the CTE barely ahead of the NUMBERS trick. – OMG Ponies Oct 15 '10 at 21:20
  • @OMG - I found the same results as Jeff Moden when doing my own testing and looking at the actual cpu, reads, and duration. Doing `SELECT COUNT(*) FROM ` took massively longer than doing `SELECT COUNT(*) FROM ` – Martin Smith Oct 15 '10 at 21:27
  • 1
    `;WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1),L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),L5 AS (SELECT 1 AS c FROM L4 A CROSS JOIN L4 B),Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L5) SELECT COUNT(n) FROM Nums WHERE n<=1000000` takes less than 1 second. – Martin Smith Oct 15 '10 at 21:30
  • `;WITH Nums AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM Nums WHERE n < 1000000) SELECT COUNT(n) FROM Nums WHERE n<=1000000 OPTION (MAXRECURSION 0);` takes > 20 seconds on my desktop. – Martin Smith Oct 15 '10 at 21:32
  • 1
    @Martin Smith: The recursive one took 15 seconds on our Dev box, waiting to see if the co-lo staff will call me about it =) – OMG Ponies Oct 15 '10 at 21:45
  • "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." – Zack Peterson Apr 26 '12 at 14:25
  • Its better define dates without the '-' (ISO format YYYYMMDD). In my database this solution not worked. – Onaiggac Aug 22 '14 at 12:30
  • 1
    Add `OPTION (MAXRECURSION 0)` to the end of the queries to avoid `The statement terminated. The maximum recursion 100 has been exhausted before statement completion` error. – Alexander Abakumov Sep 15 '14 at 17:23
  • Recursive is almost never a salable solution. See "Martin Smith" solution here taken from Itzik original idea. It scales well for any number of days and outperforms anything else except for having a physical table in place. – Greg Z. Jan 03 '19 at 17:53
20

You need a numbers table. If you don't have a permanent one this is a more efficient way of generating one than using a recursive CTE. A permanent one will be more efficient though as long as it is read from the buffer cache.

DECLARE @D1 DATE = '2010-12-01'
DECLARE @D2 DATE = '2010-12-04'

;WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4)
SELECT DATEADD(day,i-1,@D1)
 FROM Nums where i <= 1+DATEDIFF(day,@D1,@D2)
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 5
    This should be marked as the answer, not the Recursive CTE. Recursion is a loop and loops are slower in SQL. Note that Itzik's original example of this technique uses TOP to restrict the cross joins from going too far. "Where i <=" is not going to stop the cross joins, see his article: http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers – Davos Apr 07 '14 at 06:15
  • 4
    Change the Select part to: SELECT TOP (1+DATEDIFF(day,@D1,@D2)) DATEADD(day,i-1,@D1) FROM Nums – Davos Apr 07 '14 at 06:18
  • 2
    This is by far the best answer, and doesn't suffer from recursion problems. – Steve Sether Sep 15 '15 at 17:22
  • Hi @martin-smith, Brilliant answer! Please explain the above logic in detail. It would be great to know it. – vCillusion Jun 06 '18 at 20:01
  • @vCillusion the explanation is on the sqlmag link. Essentially, the cross join unions create a bunch of `1`s (65536 of them, add another L5 to get more) and the `ROW_NUMBER()` then gives them an index. – freedomn-m Sep 27 '18 at 08:38
6

I just did something like this:

declare @dt datetime = '2010-12-01'
declare @dtEnd datetime = '2010-12-04'

WHILE (@dt < @dtEnd) BEGIN
    insert into table(datefield)
        values(@dt)
    SET @dt = DATEADD(day, 1, @dt)
END
NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Thanks, but one of the requirements was not to use a loop (see original question). – dcp Oct 15 '10 at 23:57
2

Repeated Question

Getting Dates between a range of dates

DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom='20000101';
SET @DateTo='20081231';
-------------------------------
WITH T(date)
AS
( 
SELECT @DateFrom 
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);
Community
  • 1
  • 1
SDReyes
  • 9,798
  • 16
  • 53
  • 92