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

   

11 Comments on “Generate List Dates between Date Range”

  1. In your 2nd method, WHERE clause has to be… Am I correct ? Frown

    1 WHERE [dates]+1 <= @dt2

  2. 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;

  3. 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

  4. nice concept by marc,Osama.

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

  5. 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?

  6. 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

  7. 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.