SQL Server Function to return a range of dates

Problem

Have you ever been asked to create a report showing totals across a range of dates? Not too terribly difficult, is it? A simple GROUP clause and a SUM or other group function and you're done. But, what if you want to include those days that don't have any data? Then, it comes a bit more challenging.

A quick solution is a table function that will create a date range based on the parameters given. Then, you can just join to the results.

Solution

As with most problems in SQL Server, multiple answers exist. For instance, in one variation, the number of days needed was calculated. This number then determined how many cross joins would be used to populate a table variable. The table variable would then be sorted using the ROW_NUMBER function. The individual row number was then added to the starting date to create the date range. Sound complicated? It was.

The other examples that I've found were just as complicated or had some fundamental error that made them only usable under very narrow circumstances. I didn't like any of them, so I developed one of my own. The code and explanation is included below.

Function Definition

The first step to creating the function is to decide exactly what's going to be passed to it. For this function, I chose three parameters. The starting and ending dates were required, of course. After all, it's difficult to create a range if you don't have the range. I also decided to add an increment parameter. This parameter will determine if the range of dates is by day, month or year depending on the value that's passed in.

Since this function will need to return a range of values, it only makes sense to make it a table function. Similarly, it is a date range function, so the table that's returned will contain nothing but dates.

CREATE FUNCTION [dbo].[DateRange]
(     

Increment              CHAR(1),
      
@@StartDate              DATETIME,

IME
)
RETURNS  
@SelectedRange    TABL
@EndDate                DATE
TE 

ividualDate DATETIME)

(In
d

Creating the Range

Once the parameters have been chosen, it's time to build the function. The code to create the list of dates is fairly simple. I wanted something that could be transported from database to database, so I didn't want to rely on any database object. Instead, I used a Recursive CTE.

The first value that the CTE returns is the start date. The subsequent values is the previous date added to the increment. Therefore, for the second value, the function takes the start date and adds either a day, a week or a month do it depending on the increment chosen. The next value after that adds another, and so forth.

The function knows that it's time to stop when it reaches the end date. Specifically, it checks to make sure the current value is less than or equal to the ending date minus the increment. It does this to ensure that the next increment won't take the date value past the end date.

The results of the CTE are put into the table that will be returned from the function. By default, CTEs have a maximum recursion of 100. So, for this function, only 100 days could be returned. I felt that wasn't sufficient. So, I used the OPTION keyword to reset the MAXRECURSION to 3660, i.e. just over 10 years if using a daily increment. If any more is needed, it'd be fairly simple to change that value.

;WITH cteRange (DateRange) AS (
    SELECT @StartDate

CT 
        C
UNION ALL
    SEL
EASE

       WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
    
WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)

e)
        END
    FROM cteRange
    WHERE DateRange <= 
       
WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRan
g CASE
            WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)

     WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
      
WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
      
   END)
INSERT INTO @SelectedRange (IndividualDate)
SELECT DateRange
FROM cteRange
OPTION (MAXRECURSION 3660);

Putting it Together

Now that each part has been built, here's the code for the full function.

CREATE FUNCTION [dbo].[DateRange]
(     

Increment              CHAR(1),
      
@@StartDate              DATETIME,

IME
)
RETURNS  
@SelectedRange    TABL
@EndDate                DATE
TE 
(IndividualDate DATETIME)
AS 
BEGIN

          SELECT @StartDate
         
;WITH cteRange (DateRange) AS (
 
    UNION ALL
            SELECT 
                  CASE

D(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEA
WHEN @Increment = 'd' THEN DATEA
DDD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END

= 'd' THEN DATEADD(dd, -1
FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment
 , @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)

     END)
          
      INSERT INTO @SelectedRange (IndividualDate)
     
WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
            
  SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END


GO

Running a Sample

Now that the function is built, it's time to test. The query below is looking for every day between November 1st, 2012 and November 10th, 2012. I picked these dates at random.

SELECT IndividualDate FROM DateRange('d', '11/01/2012', '11/10/2012')

Here's the results of the query.

Joining to Another Table

In the example below I am creating a temporary Orders table that has a few orders.  I can use the function to show a list of orders by date as well as any dates that have gaps as shown below.

CREATE TABLE #temp (orderDate DATETIME, orderInfo VARCHAR(50))

INSERT INTO #temp VALUES ('11/1/2012','2 copies of SQL Server 2008')

INSERT INTO #temp VALUES ('11/5/2012','6 copies of SQL Server 2008 R2')
INSERT INTO #temp VALUES ('11/5/2012','10 copies of SQL Server 2012')





SELECT a.IndividualDate 

FROM DateRange('d', '11/01/2012', '11/10/2012') as a 

LEFT JOIN #temp as b on a.IndividualDate = b.orderDate

Finding Dates With No Activity

Another report need may be to find dates where there was no activity as shown below.

SELECT *

FROM DateRange('d', '11/01/2012', '11/10/2012') as a 

LEFT JOIN #temp as b on a.IndividualDate = b.orderDate

WHERE b.orderDate IS NULL