SQL Server Function to return a range of dates

By: Albert Hetzel   |   Comments (27)   |   Related: > 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,
      @EndDate                DATETIME
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)

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
    UNION ALL
    SELECT 
        CASE
            WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
            WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
            WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
        END
    FROM cteRange
    WHERE DateRange <= 
        CASE
            WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
            WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
            WHEN @Increment = 'm' THEN DATEADD(mm, -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,
      @EndDate                DATETIME
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)
AS 
BEGIN
      ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)
          
      INSERT INTO @SelectedRange (IndividualDate)
      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

Next Steps

• The function above should be database neutral. In other words, the code can be compiled onto any SQL Server 2005 or later database. From there, it's just a matter of calling it when you need a range.
• I could have used the DATE data type instead, but I wanted this to work for SQL Server 2005 and later versions.
• While this function was built with only the day, week and month increments. It might be interesting to add all the same increments as you would get with some of the built in functions. As an example, review the DATEADD function on MSDN.
• Be sure to checkout these related tips:
  • Determine SQL Server Date and Time with DATEPART and DATENAME Functions
  • Add and Subtract Dates using DATEADD in SQL Server
  • Recursive Queries using Common Table Expressions (CTE) in SQL Server

About the author

Albert Hetzel works as a SQL Server Developer for a Dallas technology company.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips