Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

SQL Server Function to return a range of dates

MSSQLTips author Albert Hetzel By:   |   Read Comments (18)   |   Related Tips: More > 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.

DateRange Results

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

date range join to table

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

date range no activity
Next Steps


Last Update: 10/25/2012


About the author
MSSQLTips author Albert Hetzel
Albert Hetzel works as a SQL Server Developer for a Dallas technology company.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, October 25, 2012 - 10:06:46 AM - Tom Read The Tip

Thanks Albert - good idea.  I had been creating temp tables but I think this makes better sense.

-- Added increments for hourly and quarterly

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 = 'h' THEN DATEADD(hh, 1, DateRange)
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                        WHEN @Increment = 'q' THEN DATEADD(qq, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <=
                  CASE
      WHEN @Increment = 'h' THEN DATEADD(hh, -1, @EndDate)
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                        WHEN @Increment = 'q' THEN DATEADD(qq, -1, @EndDate)
                  END)
         
      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END
GO


Friday, October 26, 2012 - 5:36:28 AM - Paul Read The Tip

This is excellent, very useful - thank you


Thursday, November 01, 2012 - 10:22:57 AM - Paul Dennis Read The Tip

Hey there,

Thanks for the great info.  I've accomplished something similar by using a WHILE loop, and I'm wondering if there's a reason you decided to use a recursive CTE instead.  Is there a performance advantage?

Thanks!

Paul


Thursday, November 01, 2012 - 11:19:56 AM - Albert Hetzel Read The Tip

A couple of reasons:

  1. I avoid loops whenever I can. Although I doubt there'd be much of a performance difference in this case, it's not a good practice to create loops if they can be avoided.
  2. When I first created that code, it was part of an on-the-fly query. I needed a single SQL Statement that could be written quickly. It was only later that I split out that code into a function.

Albert


Thursday, November 01, 2012 - 3:04:10 PM - Paul Dennis Read The Tip

Ok, thanks Albert for the great explanation, much appreciated!


Friday, November 02, 2012 - 4:34:23 AM - Dave Ballantyne Read The Tip

Hi Albert,

 

In my experience Recursive CTEs are rarely the most optimal solution.

Even a 'naive'  tally table will outperform this soln

select dateadd(dd,number,'20120101') as Date
 from master.dbo.spt_values
where type='P'
and number between 0 and 364

Taking that one step further and pushing to a calendar table

Create Table CalendarTab
(
  IndividualDate date primary key
)
go
insert into CalendarTab(IndividualDate)
select dateadd(dd,number,'20120101') as Date
 from master.dbo.spt_values
where type='P'
and number between 0 and 364
go

select * from CalendarTab where IndividualDate between '20120101' and '20121230'

Things get even better.

Not only are we faster, but we have statistics so the estimated row counts are spot on.

Would like to hear your thoughts


Friday, November 02, 2012 - 5:29:21 AM - Phil Factor Read The Tip

I wouldn't want to assume that a recursive CTE solution will out-perform a properly written WHILE loop. It is dangerous even to assume that the WHILE loop is always bad.

My advice to anyone is to run performance tests on this sort of routine, comparing with other solutions. Whenever I try to guess how a routine will perform, I usually get it wrong.

Dave is absolutely right in his comment. His solution is always likely to be the most effective, and having a number or calendar table is no bar to portability. The key to its good performance is having accurate rowcount estimation for the query optimiser.


Friday, November 02, 2012 - 9:33:57 AM - Paul Read The Tip

Thanks Albert,

For readers that would prefer the output to be resolved to the begining of whatever time bracket is provided (d, m, q, whatever), they can add this block prior to the CTE block to zero out the seed date. 

    -- ---------------------------------------------------
    -- Resolve starting date to zero hour of the first increment
    -- identified by the parameter
    -- ---------------------------------------------------
    set @StartDate = case   
        WHEN @Increment = 'd' THEN DATEADD(day, DATEDIFF(day, 0, @StartDate), 0)
        WHEN @Increment = 'w' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartDate), 0)
        WHEN @Increment = 'm' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)
        WHEN @Increment = 'q' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0)
        WHEN @Increment = 'y' THEN DATEADD(year, DATEDIFF(year, 0, @StartDate), 0)
        END

... Continue with the rest of the function.


Friday, November 02, 2012 - 10:28:13 AM - John Goodwin Read The Tip

###############################
Your method
###############################
-------------------------------
'd', '2012-03-01', '2012-04-01'
Table '#07020F21'. Scan count 0, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(32 row(s) affected)

(32 row(s) affected)
Table '#07020F21'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-------------------------------
'd', '2002-03-01', '2012-03-08'
Table '#145C0A3F'. Scan count 0, logical reads 3668, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 21968, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(3661 row(s) affected)

(3661 row(s) affected)
Table '#145C0A3F'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

###############################
My method
###############################
-------------------------------
'd', '2012-03-01', '2012-04-01'
(32 row(s) affected)
Table 'NumberValues'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-------------------------------
'd', '2002-03-01', '2012-03-08'
(3661 row(s) affected)
Table 'NumberValues'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here's how my method works:

/*
Setup the data
*/
CREATE TABLE [dbo].[NumberValues] ([NumberVal] [int] IDENTITY(1, 1) PRIMARY KEY CLUSTERED) ON [PRIMARY]

SET IDENTITY_INSERT dbo.[NumberValues] ON

DECLARE @StopVal INT

SET @StopVal = 3661;

WITH TEST
AS (
 SELECT 1 VAL
 
 UNION ALL
 
 SELECT T.VAL + 1
 FROM TEST T
 WHERE T.VAL + 1  )
INSERT INTO NumberValues (NumberVal)
SELECT VAL
FROM TEST T
WHERE T.VALOPTION (MAXRECURSION 3660);

SET IDENTITY_INSERT dbo.[NumberValues] OFF

/*
Run the test
*/
SET STATISTICS IO ON

DECLARE @SelectedRange TABLE (IndividualDate DATETIME)
DECLARE @StartDate DATETIME
 , @EndDate DATETIME
 , @Increment CHAR(1)
SET @Increment = 'd'
SET @StartDate = '2002-03-01'
SET @EndDate = '2012-03-08';

WITH cteRange(DateRange) AS (
SELECT
 CASE
 WHEN @Increment = 'd'
  THEN DATEADD(dd, NumberVal-1, @StartDate)
 WHEN @Increment = 'w'
  THEN DATEADD(ww, NumberVal-1, @StartDate)
 WHEN @Increment = 'm'
  THEN DATEADD(mm, NumberVal-1, @StartDate)
 END AS IndividualDate
FROM NumberValues
)

SELECT *
FROM cteRange
WHERE DateRange BETWEEN @StartDate AND @EndDate
SET STATISTICS IO OFF

In short, you join against a table of numbers, and use the numbers instead of trying to loop.

Also note that the spt_values being a similar idea doesn't have a controllable number of rows.

John


Friday, November 02, 2012 - 10:43:41 AM - Keith Read The Tip

Thank You Albert

I'm sure Dave and Phil are technically correct but ... It doesn't seem to me that performance is an issue with this.  How many time periods is someone likely to request? 13 or 52 weeks? or 365 days? And I prefer to avoid creating unnessary tables.  Anyway.

I added one parameter @IncrementCount int = null and changed @EndDate to = null. Then I added following to the start of the function which lets you specify how many increments (periods) you want without having to determine the @EndDate to pass.  If the increment is negative you get dates going back x periods up to the supplied @StartDate.  The latter is very helpful in my world - think rolling 13 weeks. I'm sure someone out there can make this a lot prettier.

Call it like this select * from dbo.DateRange('d', '10/1/2012',null,-4) for a quick look.

if isnull(@IncrementCount,0) > 0
    begin

    set @EndDate = CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, @IncrementCount - 1, @StartDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, @IncrementCount - 1, @StartDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, @IncrementCount - 1 , @StartDate)
                    END
    end

if isnull(@IncrementCount,0) < 0
    begin
    set @EndDate = @StartDate
    set @StartDate = CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, @IncrementCount + 1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, @IncrementCount + 1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, @IncrementCount + 1, @EndDate)
                     END
    end

 

 


Friday, November 02, 2012 - 5:50:17 PM - Jeff Moden Read The Tip

First of all, let me say that this is a very well written article and it's a real pleasure to see such nicely formatted and easy to read code.  Well done, Albert.

That being said, the others are correct about the resursive CTE (rCTE for short) being a performance problem.  And, while it may be true that this code might not be executed often thereby supposedly justifying such a performance and resource problem with low rowcounts and low usage, the damage may be done when someone picks up on the method for something with much higher usage even if not used for the same purpose.  A "newbie" or someone desparate for the solution may believe that using rCTEs to do something with a "count" are ok to use simply because they don't contain a WHILE loop.

Nothing could be further from the truth.  Please see the article about this at the following URL.

http://www.sqlservercentral.com/articles/T-SQL/74118/

Not all rCTEs are bad... just the ones that count in a RBAR fashion are.


Friday, November 02, 2012 - 6:42:32 PM - Peter Schott Read The Tip

Interesting function (and discussion). I ended up creating a calendar table, but mostly because we're not just looking up dates, but also other information about those dates (1st day of month, last day of month, week of month, last week of month, number of the occurrence of this weekday in the month - e.g. 2nd Tueday or Last Friday).

I can see where any of these would be useful for a quick calendar control, though. For us, it was helpful to build the Calendar table with all of the other data attached and just pull the appropriate date range. It definitely helped some of our queries quite a bit, especially the ones that calculate dates for some really odd schedules. :)


Tuesday, November 06, 2012 - 7:48:24 PM - Albert Hetzel Read The Tip

Thanks to all who commented.


Monday, February 25, 2013 - 9:19:46 AM - Joe Celko Read The Tip

Wouldn't it be easier to simply have a CALENDAR table and extract a subset? A century of dates is only 36,525 rows. The cal_date primary key will be in DESC order for speed and the data will be packed solid, no fill. A table done once versus the overhead of repeating a recursive query (really a loop and cursor in disguise) seems much better. 


Sunday, March 10, 2013 - 12:45:42 AM - Sandeep Mittal Read The Tip

Also, Refer this post

http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html


Wednesday, May 01, 2013 - 5:28:57 PM - David Sumlin Read The Tip

If you have the ability/luxury of installing CLR on the server, there's a free SQL CLR library called SQL# at www.sqlsharp.com  With that library, there are two table valued functions called:  These are even more flexible and are super duper simple to use. 

  • Util_GenerateDateTimeRange
    • @StartDateTime datetime
    • @EndDateTime datetime
    • @Step int  (<> 0)
    • @StepType nvarchar(4000) (allowable values below)
      • year
      • month
      • day
      • hour
      • minute
      • second
      • millisecond
  • Util_GenerateDateTimes
    • @StartDateTime datetime
    • @EndDateTime datetime
    • @TotalDateTimes int  (>= 0)
    • @Step int (<> 0)
    • @StepType nvarchar(4000) (allowable values below)
      • year
      • month
      • day
      • hour
      • minute
      • second
      • millisecond

Wednesday, November 13, 2013 - 11:18:59 AM - Mathieu Laforce Read The Tip

Very interesting article, thanks to all who participated.

I'm new with CTE and i would like to understand something in the example provided by Albert.

Why using the function DateRange as "parameter" to cteRange?

Should'nt it be column names?

Is it implicitely referering to (IndividualDate), the only field in the table returned by the DateRange function?

I can read about WITH usage in different webpages, but i cannot find any explication about this.

 

Thanks in advance!


Friday, February 07, 2014 - 4:20:02 AM - Kostas Tsiolis Read The Tip

I would like to mention a bug .

The following query 

SELECT IndividualDate FROM DateRange('m', '29/01/2014', '28/02/2014')

returns only 

2014-01-29 00:00:00.000

Though, the following query 

SELECT IndividualDate FROM DateRange('m', '28/01/2014', '28/02/2014')

returns 

2014-01-28 00:00:00.000

2014-02-28 00:00:00.000

This happens because the CTE is based on difference of the Starting - Ending Date. 

In the first case  , the difference is LESS than a month so only the first month of the range is returned.

In the second case, the difference is MORE than a month so BOTH months are returned.

This issue is fixed by calibrating the Starting Date to the "Start" of the TimeSpan it refers.

Meaning .. if we want to get range for example of months ... then the actual start date is the first day of the month which refers to the start date. In code :

SELECT @StartDate=CASEWHEN @Increment = 'h' THEN CONVERT(VARCHAR(13),@StartDate,120)+':00'

WHEN @Increment = 'd' THEN CONVERT(VARCHAR(10),@StartDate,120)+' 00:00'

WHEN @Increment = 'm' THEN CONVERT(VARCHAR(7),@StartDate,120)+'-01 00:00'

END

The same issue as refered from the solution , exists also for Days and Hours.

(for example try SELECT IndividualDate FROM DateRange('h', '01/01/2014 15:59', '01/01/2014 16:00'))

For any questions , argues , please do not hesitate.

If I helped ..



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.