Getting all (calendar)days within a period

Goal

Sometimes you need a list with all days within a specific period, for example to outer join with other tables to have a complete calendar overview. Here is a simple way to get those days.

Solution

Create the Table-Valued Function:

 

CREATE FUNCTION fnGetCalendarDaysInPeriod

(

       @StartDate DATE,

       @EndDate DATE

)

RETURNS TABLE

AS

RETURN

(

       WITH mycte AS

       (

         SELECT CAST(@StartDate AS DATE) DateValue

         UNION ALL

         SELECT  DATEADD(dd, 1, DateValue)

         FROM    mycte  

         WHERE   DATEADD(dd, 1, DateValue) < @EndDate

       )

 

       SELECT  DateValue

       FROM    mycte

)

 

The Function CALL:

DECLARE @StartDate DATE

DECLARE @EndDate DATE

 

SET @StartDate = DATEADD(yy, -2, GETDATE())

SET @EndDate = GETDATE()

 

 

SELECT *

FROM fnGetCalendarDaysInPeriod (@StartDate,@EndDate)

OPTION (MAXRECURSION 0)

Note

Use the Option “MAXRECURSION 0”, otherwise you will exceed the limit of maximum 100 recursions (Default Value).  The “OPTION” clause can be used only at the statement level, so you can’t put this in a “RETURN”.

 

Workaround MAXRECURSION problem:

Return a Table Variable so you can put the “OPTION(MAXRECURSION)” in the “INSERT INTO” statement

CREATE FUNCTION [dbo].[fnGetCalendarDaysInPeriod]

(    

      @StartDate              DATETIME,

      @EndDate                DATETIME

)

RETURNS 

@SelectedRange    TABLE

(IndividualDate DATETIME)

AS

BEGIN

       WITH mycte AS

       (

         SELECT CAST(@StartDate AS DATE) DateValue

         UNION ALL

         SELECT  DATEADD(dd, 1, DateValue)

         FROM    mycte  

         WHERE   DATEADD(dd, 1, DateValue) < @EndDate

       )

 

       INSERT INTO @SelectedRange

       SELECT  DateValue

       FROM    mycte

       OPTION (MAXRECURSION 0);

 

      RETURN

END

GO

 

This will make life much easier for the Developers; they don’t have to care about the “MAXRECURSION” anymore.

Note 2

Using a table valued function can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on. SQLServer will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then therefore the plan generated could be a lot less than optimal.