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;
Excellent function! Works like a charm and thank you for sharing.
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.
Hi, Lou!
Good idea. Time allowing, I’ll try to write something on the subject in the near future!
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 ?
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?
thanks
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, DEFAULT,
DEFAULT, DEFAULT,
Default, 1, — Every month
1, — Every year
{d ‘2000-01-01’}, — From 2000..
{d ‘2099-12-31’}, — … to 2099
DEFAULT) AS leapYearDays;
Thanks
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, DEFAULT,
DEFAULT, DEFAULT,
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 🙂
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?
SELECT * FROM dbo.fn_recurringDates(1, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, GETDATE(),DEFAULT, 10) as nex10mondays
Thank you very much!
Interesting. Let me check and get back to you.
I’m very likely holding this wrong but I believe the modulo filters may not be quite working correctly as intended.
Taking your election example but plugging in US pattern net’s us the 2nd and 4th tuesdays on each year. I’m just hoping I am missing something at the moment.
SELECT *
FROM dbo.fn_RecurringDates(
2, 2, DEFAULT, — 2nd Tuesday
DEFAULT, DEFAULT,
DEFAULT, DEFAULT,
1024, 1, — Every Nov
4, — Every four years
{d ‘2008-01-01’}, — From 2008..
DEFAULT,
10 — … up to 10 occurrences.
) AS [US ElectionDays];
If memory serves, election day falls on the first tuesday _after the first monday_ of november. Could that be the problem you’re seeing?
Fair enough on the stated goal being incorrect (troubles of not being American is some of the specifics of the election cycle may be incorrect) but I think that’s rather missing the underlying point. What am I missing then with the above if the call then is to produce the 2nd Tuesday in Nov in a 4 year cycle? From my reading of the inputs shouldn’t that actually produce the results of
-2008-11-11
-2012-11-13
-2016-11-08
…
but what you get is :
–2008-11-11
–2008-11-25
–2012-11-13
–2012-11-27
–2016-11-08
–2016-11-22
I’m was pretty sure I understood the intended inputs but am also under no illusions that I may have overlooked something stupidly simple. That’s rather why I dislike functions with dissimilar and contextually exclusive inputs as they’re too easy to miss something subtle (or as it is often called, “fails the two beers test” 🙂 ). That’s not your fault, mind you because as you pointed out this is very much use at your own risk nor are you claiming it is production ready.
Ok, I think I see the problem. The results you’re getting are “every _second/other_ tuesday of november every four years”, whereas you’re looking for “_the second_ tuesday of november every four years”.
You may think this as a workaround, but it gives me the results I expect:
SELECT *
FROM dbo.fn_RecurringDates(
2, 1, DEFAULT, — Every Tuesday that is…
2, 2, — the 2nd one of the month
DEFAULT, DEFAULT,
1024, 1, — Every Nov
4, — Every four years
{d ‘2008-01-01’}, — From 2008..
DEFAULT,
10 — … up to 10 occurrences.
) AS [US ElectionDays];
Have a great weekend!
Ahhh sweet! Thanks-I was sure I was holding it wrong. 🙂
Outstanding work Daniel! I am not sure what I am doing wrong but the code below seems to work, but its having a fault. I am looking for every other Tuesday starting with 7/1/2014. The following seems to work, except it leaves out the 6/30/2015 interval and is then subsequently off by one week. This seems to happen near or around the 1 year mark from the starting date. I cant figure out of its something I am doing wrong, or in the procedure itself.
SELECT *
FROM dbo.fn_recurringDates(
2,2,DEFAULT,
DEFAULT,DEFAULT,
1,DEFAULT,
DEFAULT,DEFAULT,
DEFAULT,
‘6/24/2014′,
’12/31/2016’,
DEFAULT)
Thanks! I’ll check back on this when I have some time.
Thanks for the quick response. I have made some headway, in that I think this boils down to a problem when the year in question resolves to 53 weeks. At that point I think the modulo fails to take into account. I haven’t reached a solution.
Have the same issue as Kevin. Is there a workaround for this?
Thanks,
I haven’t found one yet. I have tried eliminate the 53rd week and resetting variables to avoid the issue but I haven’t been successful.
Did you ever find a solution?
Sadly, no.
As Brad (2014) and Lluis (2016-09-30) have commented @occurrences doesn’t work for periods less than a year; @end works fine and the substitute of adding @occurrences with a multiplier to @start may sometimes work.
Presuming this wasn’t intended as an exercise for the reader I have worked it out for some cases but it involves changes the fn rather than a combination of parameters.
So, are you interested in factoring in runs like
10 working days starting 2017-01-01 (which produces a run for a year at present)
basically anything with @occurrences ending within a year will produce an unexpected set.
What I’m not sure about is how general my mods are.
Comments welcomed, untidy code available in principle but I’d prefer DanH to give some indication of how he’d like this dealt with.
—
Wm
Hey, thanks!
I haven’t really had the time to look into this matter yet, but I think you’re on to something.
Great program, thank you. I am getting a strange result when trying to schedule the 2nd Thurdsday of every 4 months. It seems to depend on the start date, but doesn’t include anything in the current month. That’s okay but I am getting a strange result in the second example below.
Example 1 Start date 2017-01-01
SELECT * FROM dbo.fn_recurringDates(
8, DEFAULT, default, — Thursday
2, 2, — Second of the month, in..
DEFAULT, DEFAULT,
DEFAULT, 4, — .. every 4th month
DEFAULT,
{d ‘2017-01-01’},
{d ‘2019-01-01’},
DEFAULT);
…
2017-04-13
2017-08-10
2017-12-14
2018-04-12
2018-08-09
2018-12-13
Example 2 Start date 2017-01-26
SELECT * FROM dbo.fn_recurringDates(
8, DEFAULT, default, — Thursday
2, 2, — Second of the month, in..
DEFAULT, DEFAULT,
DEFAULT, 4, — .. every 4th month
DEFAULT,
{d ‘2017-01-26’},
{d ‘2019-01-01’},
DEFAULT);
…
2017-04-13
2017-08-10
2017-12-14
2018-05-10
2018-09-13
From 2017 to 2018 spans 5 months? Perhaps I am missing something in the intended implementation of occurrences. Thanks for any help!
Great work, thanks. Exactly what I was looking for!
I also found some strange behaviours which I think are due to the @year date initialized with @start. The ordinal values are offset.
I made the following changes :
initialize with @year date = datefromparts(datepart(yy, @start), 1, 1) — instead of @year = @start
and filter the first excess dates with … AND sub.[date] >= @start
Those changes fixed my problem and some other signaled above. Hope this helps !
Excellent! Thanks for the feedback, Stéphane!
Thank you. The function is really helpful. Is there a way to get the first weekday of every 1 month?
and the first weekend day of every 1 month?
You should check out my calendar dimension project. Using that framework, you could leverage the following query:
SELECT DATEFROMPARTS([Year], [Month], 1),
MIN((CASE WHEN ISO_weekday_number IN (1, 2, 3, 4, 5) THEN [Date] END)) AS FirstWeekdayOfMonth_ISO,
MIN((CASE WHEN US_weekday_number IN (6, 7) THEN [Date] END)) AS FirstWeekendOfMonth_ISO,
MIN((CASE WHEN US_weekday_number IN (1, 2, 3, 4, 5) THEN [Date] END)) AS FirstWeekdayOfMonth_US,
MIN((CASE WHEN US_weekday_number IN (6, 7) THEN [Date] END)) AS FirstWeekendOfMonth_US
FROM Calendar.Dates({d '1990-01-01'}, {d '2029-12-31'})
GROUP BY [Year], [Month]
ORDER BY 1;
Good luck!
Hi, Great Function! I discovered a bug in weekFrequency when week 1 is in the old year.
I changed
DENSE_RANK() OVER (
ORDER BY DATEPART(yy, [date]),
NULLIF(DATEPART(isoww, [date]), 0)
) AS wkOrdinal,
to
DENSE_RANK() OVER
ORDER BY CASE
WHEN DATEPART(isoww, [date])=1 AND DATEPART(mm, [date])=12
THEN DATEPART(yy, [date])+1
ELSE DATEPART(yy, [date])
END,
NULLIF(DATEPART(isoww, [date]), 0)
) AS wkOrdinal,
Can you put this function on Github? would be easier to participate..thx great work
Awesome, thank you Adrian!
I’ll put it on Github as soon as possible.
I finally got around to putting the function in a Github repo. Thanks again for your contribution!
https://github.com/dhmacher/recurringdates
Hi,
Great function looking for it for so many months thanks…
I am just trying this function and wondered what would be the case for getting recursive dates for every month 3rd and 5th week.
Please help me
Have you seen a PostgreSQL-compatible version of your T-SQL?
Not that I know of. I’m not really up-to-date on PostgreSQL.
Optionally, is there a pseudo-code version of this anywhere?
Looks good. Why a function? Seems to me this would be great as a Stored Procedure
A function can be composed as part of a select statement and is easier to work with it as a tablevar
For sequence : Every 3rd Monday of the month –
you should start loop from the beginning of the month:
IF @occurrenceType =2
SET@year =dateadd(dd,-day(@start)+1,@start)
— This loop will start off with @year=@start and then
— increase @year by one calendar year for every iteration: