The second Tuesday is a calendar rule, while every fourteen days is an anchored interval. Recurring events such as the second Tuesday or the last Friday can be generated from that rule. Stable weekday arithmetic keeps session settings from changing the answer.
Describe Recurring Events Precisely
Second Tuesday means the second occurrence of Tuesday in a calendar month. It does not mean fourteen days after the first day. Last Friday means the latest Friday in that month. Every fourteen days instead describes a fixed interval from an anchor date, which can cross month boundaries.
I write those distinctions before creating the date query. Similar-looking schedules can encode different obligations. A billing run tied to a calendar weekday should not drift because somebody implemented it as a fixed day interval. The rule needs an owner, an active date range, and a clear interpretation of exceptions.
Use SQL Server 2022 with compatibility level 160 for GENERATE_SERIES. The example creates complete calendar months before selecting occurrences. If you generate only a partial month and then rank its weekdays, the second returned Tuesday can be the third Tuesday of the actual month.
Generate Complete Calendar Days
Start with a date range covering complete months. Generate nonnegative day offsets and add them to the first date. The explicit step prevents an invalid reversed range from becoming an unintended descending series. Validate start and end dates in the calling procedure or application.
DECLARE @From date='20260101',@Through date='20261231';
SELECT DATEADD(day,value,@From) AS CalendarDate
INTO #RecurrenceDays
FROM GENERATE_SERIES(0,DATEDIFF(day,@From,@Through),1);
SELECT MIN(CalendarDate) AS FirstDay,MAX(CalendarDate) AS LastDay,
COUNT_BIG(*) AS DaysGenerated FROM #RecurrenceDays;The temporary calendar supplies candidate dates only. It does not claim every day is available for a business operation. A maintenance window can depend on working hours, closures, and dependencies outside the date rule. Keep those constraints separate so the recurrence remains understandable.
The generated range should also be bounded. Producing centuries of candidate dates on every request is unnecessary when the consumer only needs the next quarter. Define a reasonable planning horizon and regenerate as that horizon advances.
Calculate Weekdays Without DATEFIRST
DATEPART(weekday, date) depends on the session's DATEFIRST setting. Use a known Monday anchor and a normalized modulo instead. The normalized expression handles dates before the anchor as well as dates after it. Monday maps to zero, Tuesday to one, and Friday to four.
SELECT CalendarDate,
((DATEDIFF(day,CONVERT(date,'19000101'),CalendarDate)%7)+7)%7
AS MondayBasedWeekday
FROM #RecurrenceDays
WHERE ((DATEDIFF(day,CONVERT(date,'19000101'),CalendarDate)%7)+7)%7=1
ORDER BY CalendarDate;The anchor is part of the algorithm, not a business start date. Keep it constant and clearly documented. Do not replace it with the server's current date or a localized weekday string. Those substitutions would make the same stored rule depend on when or where the query ran.
Test under different DATEFIRST values to verify the output remains identical. Reset any session setting changed during the test. A recurrence query should not rely on the connection pool happening to provide a familiar weekday configuration.
Select the Second Tuesday of Each Month
Filter Tuesdays first, then number them within each month. The month key includes the year, preventing January dates from different years sharing one partition. Only after assigning occurrence numbers should you narrow the result to the consumer's requested partial date range.
WITH Tuesdays AS
(
SELECT CalendarDate,DATEFROMPARTS(YEAR(CalendarDate),MONTH(CalendarDate),1)
AS MonthStart
FROM #RecurrenceDays
WHERE ((DATEDIFF(day,CONVERT(date,'19000101'),CalendarDate)%7)+7)%7=1
), Ranked AS
(
SELECT *,ROW_NUMBER() OVER
(PARTITION BY MonthStart ORDER BY CalendarDate) AS Occurrence
FROM Tuesdays
)
SELECT CalendarDate FROM Ranked WHERE Occurrence=2 ORDER BY CalendarDate;For the third Wednesday, change the weekday and occurrence according to the defined mapping. Some fifth-weekday rules produce no date in a month. Decide whether that means skip the month or use another fallback. Do not silently substitute the fourth occurrence without an approved rule.
Select the Last Friday
Reverse the ordering inside each month's ranking. The first descending Friday is the last Friday. This avoids complicated arithmetic involving the number of days in a month. Leap years and different month lengths follow naturally from the candidate calendar.
WITH Fridays AS
(
SELECT CalendarDate,DATEFROMPARTS(YEAR(CalendarDate),MONTH(CalendarDate),1)
AS MonthStart
FROM #RecurrenceDays
WHERE ((DATEDIFF(day,CONVERT(date,'19000101'),CalendarDate)%7)+7)%7=4
), Ranked AS
(
SELECT *,ROW_NUMBER() OVER
(PARTITION BY MonthStart ORDER BY CalendarDate DESC) AS ReverseOccurrence
FROM Fridays
)
SELECT CalendarDate FROM Ranked WHERE ReverseOccurrence=1 ORDER BY CalendarDate;The final result order remains chronological even though ranking used descending dates. Those orders serve different purposes. Keep both explicit so the output is stable and the code's intent is visible.
I compare generated dates with a small manually checked calendar before deploying a rule. That confirms the business interpretation rather than only proving the query executes. Which exception should apply when the last Friday is not a working day?
Anchor Fourteen-Day Recurring Events
For fixed intervals, store an explicit anchor and test the difference modulo fourteen. The example also excludes dates before the anchor. Remove that condition only if the business intentionally wants the rule projected backward.
DECLARE @Anchor date='20260106';
SELECT CalendarDate FROM #RecurrenceDays
WHERE CalendarDate>=@Anchor
AND DATEDIFF(day,@Anchor,CalendarDate)%14=0
ORDER BY CalendarDate;An anchor-based schedule does not reset at the beginning of each month. That is exactly why it differs from an ordinal weekday rule. Preserve the original anchor when extending the planning horizon. Choosing a new anchor each time would move every later occurrence.
Store Rules for Recurring Events Apart From Exceptions
A rule table can hold rule type, weekday number, occurrence number, anchor date, interval days, active start, and active end. Validate which fields are required for each type. Do not store contradictory combinations such as both an ordinal month rule and an unrelated interval unless the interpretation is explicit.
Join generated occurrences to a maintained business calendar for closures and nonworking dates. Decide whether to skip, move forward, or move backward. Moving an occurrence can collide with another scheduled run, so the exception policy needs its own duplicate and ordering checks.
Generated dates are planning results. Completed executions belong in a separate history table with their actual outcome. Retain that distinction so changing a future rule does not rewrite the record of previous runs. A calendar rule is a recipe; the execution history records what was actually served.
If an exception moves a run, retain both its originally scheduled date and its adjusted execution date. That preserves the rule's meaning when somebody later asks why the operation ran on a different weekday. Avoid changing the stored anchor to accommodate one closure. A one-time exception should not shift every future occurrence unless the business explicitly changes the recurrence itself.
Recurring events need one agreed exception policy as well as a recurrence rule. Preserve scheduled and actual dates separately so recurring events remain auditable when a run is moved.
Related reading on this blog: Weekday Logic That Works Under Any DATEFIRST Setting and Building Demo Data With GENERATE_SERIES.
A recurring schedule is not an endless pile of date rows, it is a precise rule with anchors, boundaries, and explicit exceptions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
