“Add two weeks” works until somebody skips a holiday and shifts the whole calendar. A schedule that repeats every other week needs a fixed anchor date. Generate occurrences from that anchor, then apply exceptions without moving the underlying pattern.
Anchor Every Other Week Before the Exception Rules
A repeating schedule starts with one agreed date. That date defines the cadence. Payroll processing, an on-call handoff, and a maintenance task can all repeat fortnightly, but they do not automatically share an anchor. Use the date that belongs to the specific schedule.
I check the anchor before I check the loop. An incorrect starting Monday generates a very tidy list of incorrect Mondays. Also decide whether the schedule means calendar dates or timestamps. The examples use date values. Time-of-day and time-zone rules belong in a separate, explicit layer.
What should happen when an occurrence falls on a holiday? This article skips that occurrence. Moving it to the previous business day is a different policy. Do not implement one while documenting the other. In particular, a payroll obligation still needs its own approved handling rule. The demonstration dates are sample inputs, not a proposed calendar for your organization.
Generate Every Other Week From the Start Date
SQL Server 2022 introduced GENERATE_SERIES. It requires database compatibility level 160 or higher for the ordinary use shown here. Check your database first. Do not change production compatibility simply to run a calendar example. Use the numbers-table alternative below if the feature is unavailable.
For each integer n, DATEADD(week, 2 * n, @start) produces one occurrence. Starting n at zero includes the anchor. The example generates a finite sample horizon and stores the dates in a temporary schedule table. A primary key on the date prevents duplicate occurrences in this single schedule.
The sequence number is useful too. It preserves the original position even when a holiday removes a displayed date. Do not renumber the underlying schedule after filtering. That would make later records describe a different recurrence. For several schedules, add a schedule identifier and make the key include that identifier. A date alone cannot identify which schedule it belongs to.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
DECLARE @start date = '20300107';
DROP TABLE IF EXISTS #FortnightSchedule;
DROP TABLE IF EXISTS #ScheduleHolidays;
CREATE TABLE #FortnightSchedule
(
OccurrenceDate date NOT NULL PRIMARY KEY,
SequenceNumber int NOT NULL UNIQUE
);
CREATE TABLE #ScheduleHolidays
(
HolidayDate date NOT NULL PRIMARY KEY,
HolidayName nvarchar(100) NOT NULL
);
INSERT #FortnightSchedule (OccurrenceDate, SequenceNumber)
SELECT DATEADD(week, 2 * value, @start), value
FROM GENERATE_SERIES(0, 52, 1);
INSERT #ScheduleHolidays (HolidayDate, HolidayName)
VALUES ('20300121', N'Sample closure');
SELECT OccurrenceDate, SequenceNumber
FROM #FortnightSchedule
ORDER BY OccurrenceDate;Use a Numbers Table When Needed
A numbers table gives the same recurrence without GENERATE_SERIES. The following self-contained version creates a small table variable of nonnegative integers from digit combinations. It limits the sample to the same sequence range. For repeated production use, maintain a permanent numbers table with a primary key instead of rebuilding it in every statement.
The important expression stays unchanged. Every date comes from the anchor plus twice the sequence number in weeks. Do not take the last accepted date and add two weeks to that date. Once a holiday enters the process, that approach makes it tempting to move the cadence by accident.
Bound the requested horizon. A numbers table must contain enough values for the requested end date, and DATEADD must remain inside the date type’s range. Report an insufficient horizon rather than silently returning an incomplete calendar. Keep the sequence value an integer so the arithmetic remains easy to inspect.
DECLARE @start date = '20300107';
DECLARE @Numbers table (n int NOT NULL PRIMARY KEY);
WITH Digits AS
(
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
)
INSERT @Numbers (n)
SELECT ones.n + 10 * tens.n
FROM Digits AS ones
CROSS JOIN Digits AS tens
WHERE ones.n + 10 * tens.n <= 52;
SELECT n AS SequenceNumber,
DATEADD(week, 2 * n, @start) AS OccurrenceDate
FROM @Numbers
ORDER BY n;
Keep Holidays in Their Own Table
Store exceptions separately from the recurrence. That lets you explain why a scheduled date disappeared without changing the original series. The holiday table uses a date key so each date has one matching exception for this example. A regional calendar needs its own calendar identifier.
Use NOT EXISTS to remove matching dates from the effective list. This also avoids the NULL behavior of a NOT IN subquery. The temporary tables come from the earlier GENERATE_SERIES example. Run these follow-up queries in the same SSMS session so those tables remain available.
I keep the unfiltered dates available during review. A skipped date should be visible when you explain the policy, even when it is absent from the final operational list. Holidays do not negotiate with modulo arithmetic. Add the exception, inspect the effective schedule, and verify that the following occurrence still comes from the original anchor.
SELECT s.OccurrenceDate, s.SequenceNumber
FROM #FortnightSchedule AS s
WHERE NOT EXISTS
(SELECT 1 FROM #ScheduleHolidays AS h
WHERE h.HolidayDate = s.OccurrenceDate)
ORDER BY s.OccurrenceDate;
SELECT s.OccurrenceDate, s.SequenceNumber, h.HolidayName
FROM #FortnightSchedule AS s
JOIN #ScheduleHolidays AS h ON h.HolidayDate = s.OccurrenceDate
ORDER BY s.OccurrenceDate;Find the Next Effective Date After Today
The word “after” matters. The next query uses a strict greater-than comparison, so an occurrence today is excluded. Change it to greater than or equal only when the caller explicitly wants today included. Use one business date for the entire request.
This example again uses a fixed date for inspection. Replace it with the appropriate current business date in the real job. The query finds the earliest remaining occurrence after filtering holidays. It returns no row when the stored horizon contains no qualifying date. With the sample data, it skips the January 21 closure and returns February 4, 2030.
No row does not mean the recurring rule has ended forever. It means this schedule table has no matching occurrence within its generated range. Extend the horizon or report that limit to the caller. If every other week drives an unattended process, check horizon coverage before the process needs the next date. A reliable recurring job should not discover an empty calendar at execution time.
DECLARE @today date = '20300108';
SELECT TOP (1) s.OccurrenceDate, s.SequenceNumber
FROM #FortnightSchedule AS s
WHERE s.OccurrenceDate > @today
AND NOT EXISTS
(SELECT 1 FROM #ScheduleHolidays AS h
WHERE h.HolidayDate = s.OccurrenceDate)
ORDER BY s.OccurrenceDate;Label Every Other Week Without Sunday Surprises
For a two-week rota, calculate whole elapsed weeks from the anchor. DATEDIFF(day, @start, @check) divided by seven gives complete seven-day blocks for dates on or after the anchor. Modulo two then identifies the alternating block. Add one if your labels are week one and week two.
DATEDIFF(week, …) counts Sunday boundaries. That does not necessarily match a rota that begins on Monday or another chosen day. Using elapsed days keeps the blocks tied to your anchor and avoids a dependence on calendar week numbering. The remainder by 14 separately tells you whether the checked date lies on an occurrence date.
Reject dates before the anchor or handle them with an explicit backward-calendar policy. Integer division and negative remainders deserve deliberate treatment. The example returns NULL for pre-anchor checks. For the sample Sunday, January 20, 2030, it returns rota week 2 and no occurrence. Skipping a holiday does not change these rota labels. The underlying every other week pattern remains anchored to the same start date.
DECLARE @start date = '20300107';
DECLARE @check date = '20300120';
SELECT @start AS AnchorDate, @check AS CheckedDate,
CASE WHEN @check < @start THEN NULL
ELSE (DATEDIFF(day, @start, @check) / 7) % 2 + 1
END AS RotaWeekNumber,
CASE WHEN @check < @start THEN NULL
WHEN DATEDIFF(day, @start, @check) % 14 = 0 THEN 1
ELSE 0 END AS IsUnderlyingOccurrenceDate;Separate the Calendar From Execution History
A schedule table says when work belongs on the calendar. It does not prove that the work ran. Keep execution status, completion time, and retry information in separate records keyed to the scheduled occurrence. A retry should not generate a new recurrence date.
Before accepting the calendar, test the anchor itself, the next day, the last day of each rota week, and the next occurrence. Include a month boundary, a year boundary, and a skipped holiday. Date arithmetic handles varying month lengths without replacing the fortnight with an approximation such as twice per month.
Use a unique key when storing a permanent schedule so regeneration cannot create duplicates. Review changed holiday entries before regenerating future effective dates. Past execution records should preserve what actually happened. With a fixed anchor, explicit exceptions, and a finite horizon, the schedule becomes understandable. The next date comes from the rule, and the job history tells you whether the work followed it.
Related reading on this blog: Find Table in Every Database of SQL Server and Find Table in Every Database of SQL Server: Part 2.
A repeating schedule is not a chain of guesses, it is an anchor plus explicit exceptions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
