When you’re using Microsoft Outlook, or pretty much any other personal information manager, you can create calendar appointments that are “recurring”, i.e. you can have them repeat at a defined frequency. This, however may not only apply to your project meeting appointments, but also to some database solution. I decided to give it a go at building a table value function that returns a list of dates, based on a given set of parameters.

Prerequisites

The function I’ve written contains a table variable, a recursive common table expression and windowed functions. If you’re not familiar with these concepts, you might want to read up on the links above before moving on.

Oh, and a disclaimer: This function may not be production grade. The rule is, if you haven’t paid for it, I won’t make any guarantees as to its fitness for any purpose.

Parameters

One of the main challenges of creating a decently dynamic function for recurring dates is providing a good set of parameters. Here’s how I’ve modelled the parameters for my function:

• @wkDayPattern: A bitwise pattern for which weekdays to return. You can add these together, so 1+8+32=41 means mondays, thursdays and saturdays.
  • 1 Mondays
  • 2 Tuesdays
  • 4 Wednesdays
  • 8 Thursdays
  • 16 Fridays
  • 32 Saturdays
  • 64 Sundays
• @dayFrequency: An integer value for the frequency, i.e. 1 means “every occurrence”, 2 means “every second”, 3 “every third”, etc.
• @exactDay: A specific day of the month.
• @occurenceNo: A specific occurrence (used with @occurenceType)
  • 0 The last occurrence…
  • 1 The first occurrence…
  • 2 The second occurence…
  • … etc
• @occurrenceType: How occurrences are grouped (partitioned)
  • 1 … of the week
  • 2 … of the month
  • 3 … of the year
• @weekFrequency: Week frequency.
• @exactWeek: A specific ISO week of the year
• @monPattern: Works like the weekday pattern, a binary, additive pattern that defines which months to include:
  • 1 January
  • 2 Frebruary
  • 4 March
  • 8 April
  • 16 May
  • 32 June
  • 64 July
  • 128 August
  • 256 September
  • 512 October
  • 1024 November
  • 2048 December
• @monFrequency: Month frequency.
• @yearFrequency: Year frequency.
• @start: The start date of the recurrence pattern.
• @end: The end date of the recurrence pattern.
• @occurrences: The maximum number of occurrences. This parameter can be used with or instead of the @end parameter.

The function

As with other posts, I’ll just paste the entire source code here, with inline comments, and then add my own notes and clarifications inbetween code blocks. This function is also available as a script on the Downloads page.

CREATE FUNCTION dbo.fn_recurringDates(
    @wkDayPattern    tinyint=127,  --- 1=Mon, 2=Tue, 4=Wed, ... 127=All
    @dayFrequency    tinyint=1,    --- 1=All, 2=every second, ...
    @exactDay        tinyint=NULL, --- Specific day number of the month

    @occurrenceNo    tinyint=NULL,  -- 1=First, 2=Second, ... 0=Last
    @occurrenceType  tinyint=NULL,  -- ... of 1=Week, 2=Month, 3=Year

    @weekFrequency   tinyint=1,    --- 1=Every week, 2=Every second, etc
    @exactWeek       tinyint=NULL,  -- Specific ISO week of the year

    @monPattern      smallint=4095, -- 1=Jan, 2=Feb, 4=March, ...
    @monFrequency    tinyint=1,    --- 1=Every month, 2=Every second...

    @yearFrequency   tinyint=1,    --- 1=Every year, 2=Every two...

    @start           date,         --- Start date of recurrence
    @end             date=NULL,    --- End date of recurrence
    @occurrences     int=NULL      --- Max number of occurrences
)
RETURNS @dates TABLE (
    [date]        date NOT NULL,
    PRIMARY KEY CLUSTERED ([date])
)
AS

BEGIN
    --- Variable declarations:
    DECLARE @occurrenceCount int=0, @year date=@start;

    --- Make sure the parameters are set correctly:
    IF (@occurrences IS NULL AND @end IS NULL) RETURN;
    IF (@occurrenceNo IS NOT NULL AND @occurrenceType IS NULL)
        SET @occurrenceNo=NULL;

The first block just contains the parameters, the return table type, and a few basic sanity checks. For example, we want to make sure the function does not go off on an infinite loop. If you’re going to give users access to the function, you may want to add a few checks of your own.

The date variable @year is used to loop the results a year at a time. Here’s the main loop of the function, which goes on until the end date or maximum number of occurrences has been reached, whichever happens first.

    --- This loop will start off with @year=@start and then
    --- increase @year by one calendar year for every iteration:
    WHILE (@occurrenceCount<@occurrences AND
            DATEDIFF(yy, @start, @year)<@yearFrequency*@occurrences OR
        @year<@end) BEGIN;

A recursive common table expression generates a year worth of days, starting with @year. The main reason for limiting this recursion to a year is that we want to control the maximum number of recursions.

        --- Build a recursive common table expression that loops
        --- through every date from @year and one year forward.
        WITH dates ([date], occurrence)
        AS (
            SELECT @year, 1
            UNION ALL
            SELECT DATEADD(dd, 1, [date]), occurrence+1
            FROM dates
            WHERE DATEADD(dd, 1, [date])<DATEADD(yy, 1, @year))

Now, using this CTE, here’s the remaining query, in two parts. Part one is a subquery which contains a number of windowed functions to calculate the ordinal number of the month of the year, week of the year, day of the year, etc. It also contains the first part of the filtering logic (at the end).

        --- INSERT the result into the output table, @dates
        INSERT INTO @dates ([date])
        SELECT [date]
        FROM (
            SELECT [date],
                --- The "ordinal number of the year"
                DATEDIFF(yy, @start, @year) AS yearOrdinal,

                --- The ordinal number of the week (first week,
                --- second, third, ...) starting with @year.
                DENSE_RANK() OVER (
                    ORDER BY DATEPART(yy, [date]),
                        NULLIF(DATEPART(isoww, [date]), 0)
                    ) AS wkOrdinal,

                --- Ordinal number of the month, as of @year.
                DENSE_RANK() OVER (
                    ORDER BY DATEPART(yy, [date]), DATEPART(mm, [date])
                    ) AS monOrdinal,

                --- Ordinal number of the day, as of @year.
                ROW_NUMBER() OVER (
                    PARTITION BY DATEPART(yy, [date])
                    ORDER BY [date]
                    ) AS dayOrdinal,

                --- Ordinal number of the day, per @occurenceType,
                --- as of @year:
                ROW_NUMBER() OVER (
                    PARTITION BY (CASE @occurrenceType
                            WHEN 1 THEN DATEPART(isoww, [date])
                            WHEN 2 THEN DATEPART(mm, [date])
                            END),
                        (CASE WHEN @occurrenceType IN (1, 3)
                            THEN DATEPART(yy, [date]) END)
                    ORDER BY [date]
                    ) AS dateOrdinal,

                --- dayOrdinal (descending). Used to calculate
                --- LAST occurrence (@occurenceNo=0)
                ROW_NUMBER() OVER (
                    PARTITION BY (CASE @occurrenceType
                        WHEN 1 THEN DATEPART(isoww, [date])
                        WHEN 2 THEN DATEPART(mm, [date])
                        END),
                        (CASE WHEN @occurrenceType IN (1, 3)
                            THEN DATEPART(yy, [date]) END)
                    ORDER BY [date] DESC
                    ) AS dateOrdinalDesc

            FROM dates
            WHERE
                --- Logical AND to filter specific weekdays:
                POWER(2, (DATEPART(dw, [date])+@@DATEFIRST+5)%7)
                    & @wkDayPattern>0 AND

                --- Logical AND to filter specific months:
                POWER(2, DATEPART(mm, [date])-1)
                    & @monPattern>0 AND

                --- Filter specific ISO week numbers:
                (@exactWeek IS NULL OR
                 DATEPART(isoww, [date])=@exactWeek) AND

                --- Filter specific days of the month:
                (@exactDay IS NULL OR
                 DATEPART(dd, [date])=@exactDay)

            ) AS sub

In the subquery, we’ve now filtered which weekdays, months, ISO weeks and/or day-of-month that we want to return. This filtering is done using the bitwise AND operator (& in T-SQL).

We’ve also built a number of counters to number our remaining dates by year, month, week, day and occurrence, and these are used “outside” the subquery, to further filter the results (say, if you want every three occurrences to be returned).

All the filtering that happens on these ordinal columns (the windowed functions) is done outside the subquery:

        WHERE
            --- Modulo operator, to filter yearly frequencies:
            sub.yearOrdinal%@yearFrequency=0 AND

            --- Modulo operator, to filter monthly frequencies:
            sub.monOrdinal%@monFrequency=0 AND

            --- Modulo operator, to filter weekly frequencies:
            sub.wkOrdinal%@weekFrequency=0 AND

            --- Modulo operator, to filter daily frequencies:
            sub.dateOrdinal%@dayFrequency=0 AND

            --- Filter day ordinal:
            (@occurrenceNo IS NULL OR
             @occurrenceNo=sub.dateOrdinal OR
             @occurrenceNo=0 AND sub.dateOrdinalDesc=1) AND

            --- ... and finally, stop if we reach @end:
            sub.[date]<=ISNULL(@end, sub.[date])

        --- The default is 100, so we'll get an error if we don't
        --- explicitly allow for more recursions:
        OPTION (MAXRECURSION 366);

Did you notice the modulo operator? It’s the percent sign. If you’re not familiar with modulo, it “wraps” integers, so they repeat over and over. Example:

In T-SQL, the modulo operator is represented with a percent sign. In this case, if we want every fourth day to be returned, we’ll apply a modulo 4 to the (0-based!) day number, and select only those rows where the result is 0.

Now, we’ve added a selection of days from one year to the @dates table. All that remains is to update the @occurrenceCount counter, increase @year by one year, and repeat the loop until it’s completed.

        --- Add the number of dates that we've added to the
        --- @dates table to our counter, @occurrenceCount.
        --- Also, increase @year by one year.
        SELECT
            @occurrenceCount=@occurrenceCount+@@ROWCOUNT,
            @year=DATEADD(yy, 1, @year);
    END;

    RETURN;
END;

Examples of usage

• “Alla helgons dag”, a swedish holiday distantly related to Halloween, occurs on the first saturday of november every year:

SELECT *
FROM dbo.fn_recurringDates(
    32, 1, DEFAULT,       --- Saturday
    1, 2,                 --- First of the month, in..
    DEFAULT, DEFAULT,
    1024, DEFAULT,        --- .. november
    DEFAULT,
    GETDATE(),
    DATEADD(yy, 4, GETDATE()),
    DEFAULT) AS firstSatOfNov;

• Daylight savings time is set/reset in Europe on the last sunday of march and october, respectively:

SELECT *
FROM dbo.fn_recurringDates(
    64, 1, DEFAULT,       --- Sunday
    0, 2,                 --- Last of the month, in..
    DEFAULT, DEFAULT,
    4+512, DEFAULT,       --- ... march and october
    DEFAULT,
    GETDATE(),
    DATEADD(yy, 4, GETDATE()),
    DEFAULT) AS daylightSavingsDates;

• Leap years occur on the 29th of february, every four years:

SELECT *
FROM dbo.fn_recurringDates(
    DEFAULT, DEFAULT, 29, -- Every 29th of the month
    DEFAULT, DEFAULT,
    DEFAULT, DEFAULT,
    2, DEFAULT,          --- Every february
    4,                   --- Every four years
    {d '2000-01-01'},    --- From 2000..
    {d '2099-12-31'},    --- ... to 2099
    DEFAULT) AS leapYearDays;

• Election day in Sweden occurs every four years on the third sunday of september:

SELECT *
FROM dbo.fn_recurringDates(
    64, 3, DEFAULT,      --- Third Sunday
    DEFAULT, DEFAULT,
    DEFAULT, DEFAULT,
    256, 1,              --- Every september
    4,                   --- Every four years
    {d '2006-01-01'},    --- From 2006..
    DEFAULT,
    10                   --- ... up to 10 occurrences.
    ) AS sweElectionDays;