Generate List Dates between Date Range

Recently a developer came to me seeking my help in generate list dates between date range for a report.  Being a .Net developer he said that one solution he had is to do row by row processing either by using while loop or a cursor. We exchanged smiles and he understood what I had in my mind..

I came up with 2 solutions for this problem. One is to use a tally table and another one using a recursive CTE.

Method 1: 

DECLARE @dt1 Datetime='2012-08-01'
DECLARE @dt2 Datetime='2012-09-01'
 
;WITH ctedaterange 
     AS (SELECT [rn]=Row_number() 
                       OVER( 
                         ORDER BY (SELECT NULL)) 
         FROM   sys.objects a 
                CROSS JOIN sys.objects b 
                CROSS JOIN sys.objects c 
                CROSS JOIN sys.objects d) 
SELECT Dateadd(dd, rn, @dt1) 
FROM   ctedaterange 
WHERE  [rn] <= Datediff(dd, @dt1, @dt2)

Method 2:

DECLARE @dt1 Datetime='2012-08-01'
DECLARE @dt2 Datetime='2012-09-01'
;WITH ctedaterange 
     AS (SELECT [Dates]=@dt1 
         UNION ALL
         SELECT [dates] + 1 
         FROM   ctedaterange 
         WHERE  [dates] + 1<= @dt2) 
SELECT [dates] 
FROM   ctedaterange 
OPTION (maxrecursion 0)

Method 1 performs a bit better than method 2 for large result set.

Do let me know if you have a different way to do this.

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

11 Comments on “Generate List Dates between Date Range”

  1. Another approach is to use the calendar table. This is more set-oriented than computations

    DECLARE @start_date DATE;
    DECLARE @end_date DATE;

    SET @start_date = ‘2012-08-01’;
    SET @end_date = ‘2012-09-01’;

    SELECT cal_date
    FROM Calendar
    WHERE cal_date
    BETWEEN @start_date AND @end_date;

  2. As long as @start_date and @end_date are less than 2048 days apart:

    DECLARE @start_date [date] = CAST(‘2012-08-01’ as [date])
    DECLARE @end_date [date] = CAST(‘2012-09-01’ as [date])
    SELECT
    DATEADD(day, [v].[number], @start_date)
    FROM
    [master].[dbo].[spt_values] [v]
    WHERE
    [v].[type] = ‘P’ AND
    DATEADD(day, [v].[number], @start_date) <= @end_date

  3. nice concept by marc,Osama.

    I Remember once I stuck into such situation, and I use Second solution provided by Osama.

  4. Okay…. Uhm… I need to create a report that actually take a date range and create columns for each weekday in between the range… Any ideas on how to do this?

  5. Hi Drilene,
    to get the date part add a column DATEPART(DD,Dateadd(dd, rn, @dt1)) As Day/ Day(Dateadd(dd, rn, @dt1)) As Day to any of the above solution… use pivot to get it as a column name.. it would be more easy if you have a calendar table …

    Thanks,
    Ahmad

  6. Could this be done with hours instead of days, for example:
    ID = 1 Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM then it would return 4 rows like this
    ID =1 Instance in Time = 12/20/2016 9:00:00 AM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
    ID=1 Instance in Time = 12/20/2016 10:00:00 AM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
    ID=1 Instance in Time = 12/20/2016 11:00:00 AM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM
    ID=1 Instance in Time = 12/20/2016 12:00:00 PM Date Issued=12/20/2016 9:00:00 AM Date Completed=12/20/2016 12:00:00 PM

    any help will be greatly appreciated.

    thanks
    Cesar

Leave a Reply

Your email address will not be published. Required fields are marked *