A function to calculate recurring dates

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.


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.


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
    [date]        date NOT NULL,

    --- 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])
                        (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])
                        (CASE WHEN @occurrenceType IN (1, 3)
                            THEN DATEPART(yy, [date]) END)
                    ORDER BY [date] DESC
                    ) AS dateOrdinalDesc

            FROM dates
                --- 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:

            --- 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:

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:

Modulo 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.
            @year=DATEADD(yy, 1, @year);


Examples of usage

  • “Alla helgons dag”, a swedish holiday distantly related to Halloween, occurs on the first saturday of november every year:
FROM dbo.fn_recurringDates(
    32, 1, DEFAULT,       --- Saturday
    1, 2,                 --- First of the month, in..
    1024, DEFAULT,        --- .. november
    DATEADD(yy, 4, GETDATE()),
    DEFAULT) AS firstSatOfNov;
  • Daylight savings time is set/reset in Europe on the last sunday of march and october, respectively:
FROM dbo.fn_recurringDates(
    64, 1, DEFAULT,       --- Sunday
    0, 2,                 --- Last of the month, in..
    4+512, DEFAULT,       --- ... march and october
    DATEADD(yy, 4, GETDATE()),
    DEFAULT) AS daylightSavingsDates;
  • Leap years occur on the 29th of february, every four years:
FROM dbo.fn_recurringDates(
    DEFAULT, DEFAULT, 29, -- Every 29th of the month
    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:
FROM dbo.fn_recurringDates(
    64, 3, DEFAULT,      --- Third Sunday
    256, 1,              --- Every september
    4,                   --- Every four years
    {d '2006-01-01'},    --- From 2006..
    10                   --- ... up to 10 occurrences.
    ) AS sweElectionDays;

11 thoughts on “A function to calculate recurring dates

  1. Do you have a way to say a payment is due on the 18th of the month, if on a weekend, the next business day?

    Maybe on the 18th of the month + 0 business days, which would be the current date if it’s during the week, else the next business day. The notion of a calendar with holidays to omit.

    I have a need for this.

  2. Pingback: Calculating the date of easter sunday « Sunday morning T-SQL

  3. Not sure if I have gotten something incorrect, but there seems to be a bug ?

    Trying for every day for the next sixty days starting 31 August 2014

    SELECT * FROM dbo.fn_recurringDates(127, 1, DEFAULT, DEFAULT, DEFAULT, 1, null, 4095, 1, 1, ‘2014-08-31 05:00:00 AM’,DEFAULT, 60)

    But it is returning 365 days from that date ?

  4. Is it possible to specify multiple specific days & months
    ie. the 5th, 7th,20th of every Jan,Feb and March?
    or will I have to call separately for each day?


  5. Hi, appreciate this post is a few years old, but wondering if you can tell me how to handle a recurrence that causes the next date to be missed.

    the example I am thinking about is repeating every 29th of the month – when there is no 29th in February (3 out of 4 cases), the recurrence for February is missed. Is it possible to trap this and return the 28th in these situations ?

    To demonstrate, the below misses out the 29th Feb when it isn’t a Leap Year.

    SELECT *
    FROM dbo.fn_recurringDates(
    DEFAULT, DEFAULT, 29, — Every 29th of the month
    Default, 1, — Every month
    1, — Every year
    {d ‘2000-01-01’}, — From 2000..
    {d ‘2099-12-31’}, — … to 2099
    DEFAULT) AS leapYearDays;


    • Interesting. Maybe something like this could work (provided you’re on SQL Server 2012 or newer).

      SELECT (CASE WHEN [date]=EOMONTH([date]) --- Is this (the 28th) the last day of the month?
      THEN [date] --- ... then use the 28th
      ELSE DATEADD(day, 1, [date]) --- ... otherwise, go with the 29th
      END) AS [date]
      FROM dbo.fn_recurringDates(
      DEFAULT, DEFAULT, 28, --- Every _28th_ of the month
      Default, 1, -- Every month
      1, -- Every year
      {d ‘2000-01-01’}, -- From 2000..
      {d ‘2099-12-31’}, -- ... to 2099
      DEFAULT) AS leapYearDays

      Disclaimer: I haven’t tried this code, I just made it up. :)

      • What I was wondering was how to handle it in the stored procedure as it has a potential to drop dates in many cases – basically any time that a exact day is specified >28, so leap years is one; months with only 30 days if you specify 31st etc. I think it should default to revering to the last day of the month in these case, but my SQL is not good enough to change the Stored Procedure :-)

  6. Great function!
    Perhaps i found an litle bug. I want to calculate the next 10 mondays but function returns 52 occurrences. Is it possible that i have an error in parameters?


    Thank you very much!

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s