Friday plus one business day is not always Monday. A calendar table puts weekends, holidays, and fiscal rules in one place, instead of hiding different assumptions inside every report.
Decide What a Business Day Means
A business day depends on the organization and location. Federal holidays, bank closures, warehouse schedules, and company shutdowns do not always match. Define the required calendar before implementing date math. One shared definition prevents two reports from disagreeing about the same deadline.
I ask who owns the holiday list before asking who owns the SQL. Someone must approve exceptions and next year's dates. The database cannot infer a warehouse closure from an empty loading dock. It can only apply the rules you supplied.
The following example covers one American work calendar for 2026. Its holiday list is deliberately small and illustrative. It is not a complete holiday authority for every employer. The temporary tables keep the demonstration self-contained in one SSMS session. Persist the approved calendar in a normal table for application use.
Generate the Calendar Table Dates Once
SQL Server 2022 introduced GENERATE_SERIES, which requires compatibility level 160 or higher. The function creates integer offsets from the chosen start date. DATEADD converts each offset into a calendar date. A maintained numbers table is the alternative on older environments.
Choose coverage that extends beyond every date calculation your application needs. A table ending this December cannot answer next January's due dates. Treat missing coverage as an error condition rather than as a holiday or an empty result. Generate dates during controlled maintenance, not during every report execution.
The example uses a July fiscal-year start and names the fiscal year by its ending year. Those are explicit business conventions. If your organization follows a different month or a retail-week calendar, store that actual rule instead of relabeling these numbers.
SELECT DB_NAME() AS DatabaseName, compatibility_level
FROM sys.databases WHERE database_id = DB_ID();
CREATE TABLE #Calendar
(
CalendarDate date NOT NULL PRIMARY KEY,
WeekdayNumber tinyint NOT NULL,
IsWeekend bit NOT NULL,
FiscalMonth tinyint NOT NULL,
FiscalYear int NOT NULL,
IsHoliday bit NOT NULL DEFAULT 0,
HolidayName nvarchar(80) NULL
);
DECLARE @StartDate date = '20260101';
DECLARE @EndDate date = '20261231';
DECLARE @FiscalFirstMonth int = 7;
INSERT #Calendar
(CalendarDate, WeekdayNumber, IsWeekend, FiscalMonth, FiscalYear)
SELECT d.CalendarDate, w.WeekdayNumber,
CASE WHEN w.WeekdayNumber IN (6, 7) THEN 1 ELSE 0 END,
(MONTH(d.CalendarDate) - @FiscalFirstMonth + 12) % 12 + 1,
YEAR(d.CalendarDate) +
CASE WHEN @FiscalFirstMonth > 1
AND MONTH(d.CalendarDate) >= @FiscalFirstMonth THEN 1 ELSE 0 END
FROM GENERATE_SERIES(0, DATEDIFF(day, @StartDate, @EndDate), 1) AS g
CROSS APPLY (VALUES (DATEADD(day, g.value, @StartDate))) AS d(CalendarDate)
CROSS APPLY (VALUES
((DATEDIFF(day, CONVERT(date, '19000101', 112), d.CalendarDate) % 7 + 7) % 7 + 1)) AS w(WeekdayNumber);Keep Weekday Numbers Independent of Session Settings
DATEPART(weekday, date) depends on SET DATEFIRST. That becomes a trap when sessions use different settings. The example instead counts days from a known Monday, then applies modulo arithmetic. Monday is one and Sunday is seven under this chosen convention.
The second modulo adjustment also handles dates before the anchor correctly. Negative remainders need normalization before adding one. The table stores the resulting weekday once, so future reports do not depend on a caller's language or first-day-of-week setting.
Inspect a full week and a month boundary after generation. Check the input range and fiscal transition as well. These verification queries expose the stored rules without inventing a measured row count. Use your own output to confirm that the populated dates match the approved design.
SELECT CalendarDate, WeekdayNumber, IsWeekend, FiscalMonth, FiscalYear
FROM #Calendar
WHERE CalendarDate BETWEEN '20260629' AND '20260705'
ORDER BY CalendarDate;
SELECT MIN(CalendarDate) AS FirstDate, MAX(CalendarDate) AS LastDate,
COUNT_BIG(*) AS StoredDates
FROM #Calendar;Add Holidays to the Calendar Table From an Approved List
Store holiday dates separately and join them into the calendar. The examples include Thanksgiving and an observed Independence Day closure. In 2026, July 4 falls on Saturday, so the sample closes Friday, July 3. Your organization's observed-day policy must decide that placement.
Avoid scattering holiday CASE expressions through reporting code. One reviewed list is easier to update and audit. Preserve the holiday name beside the flag, so someone investigating a due date can see why that day was excluded. Do not assume every American workplace closes for every listed holiday.
CREATE TABLE #Holidays
(
HolidayDate date NOT NULL PRIMARY KEY,
HolidayName nvarchar(80) NOT NULL
);
INSERT #Holidays VALUES
('20260101', N'New Year''s Day'),
('20260525', N'Memorial Day'),
('20260703', N'Independence Day observed'),
('20261126', N'Thanksgiving'),
('20261225', N'Christmas');
UPDATE c
SET IsHoliday = 1, HolidayName = h.HolidayName
FROM #Calendar AS c
JOIN #Holidays AS h ON h.HolidayDate = c.CalendarDate;
SELECT CalendarDate, IsWeekend, IsHoliday, HolidayName
FROM #Calendar
WHERE IsHoliday = 1
ORDER BY CalendarDate;
Count Working Days With Explicit Boundaries
Define whether both endpoints count before writing the query. The following rule includes the start date and excludes the end date. That shape combines naturally with interval-based reporting. An inclusive end requires a deliberately different predicate.
The query also checks coverage. Both endpoints must fall within the supplied calendar's range. Otherwise a count can silently omit uncovered days. In a permanent calendar, verify continuous coverage during population as well. Checking only minimum and maximum dates does not detect a missing date in the middle.
Do you count the day an order arrives, or begin counting the following day? That business choice changes the answer more than any SQL formatting trick. Keep the interval rule next to the procedure or report that promises the due date.
DECLARE @FromDate date = '20261123';
DECLARE @UntilDate date = '20261130';
IF @FromDate > @UntilDate
THROW 50000, 'The interval start must not follow its end.', 1;
IF @FromDate < (SELECT MIN(CalendarDate) FROM #Calendar)
OR @UntilDate > (SELECT MAX(CalendarDate) FROM #Calendar)
THROW 50001, 'The calendar does not cover this interval.', 1;
SELECT COUNT_BIG(*) AS WorkingDays
FROM #Calendar
WHERE CalendarDate >= @FromDate AND CalendarDate < @UntilDate
AND IsWeekend = 0 AND IsHoliday = 0;Find the Next Business Day
Use TOP (1) over eligible dates strictly after the requested date. The strict comparison means a business date supplied as input does not return itself. For an on-or-after rule, change that comparison deliberately and name the operation accordingly.
The example checks the input range and the existence of a future answer. A missing answer at the calendar's end requires more coverage. Returning NULL and calling it a closed month would hide the problem. Keep the failure distinguishable from a valid business rule.
DECLARE @AfterDate date = '20261125';
IF NOT EXISTS (SELECT 1 FROM #Calendar WHERE CalendarDate = @AfterDate)
THROW 50002, 'The requested date is outside the calendar.', 1;
DECLARE @NextBusinessDate date;
SELECT TOP (1) @NextBusinessDate = CalendarDate
FROM #Calendar
WHERE CalendarDate > @AfterDate AND IsWeekend = 0 AND IsHoliday = 0
ORDER BY CalendarDate;
IF @NextBusinessDate IS NULL
THROW 50003, 'Extend the calendar before calculating this next date.', 1;
SELECT @NextBusinessDate AS NextBusinessDate;Find the Last Business Day of a Month
EOMONTH identifies the calendar boundary, not the final working date. Search backward among eligible dates within that month. This handles a month ending on a weekend or listed holiday. It also makes the chosen closure rule visible through ordinary predicates.
I verify month-end reports against the actual calendar rather than subtracting a fixed number of days. Consecutive closures and organization-specific shutdowns defeat that shortcut. The next query requires the complete month to be covered before searching. A month with no eligible day also receives a clear failure.
DECLARE @InMonth date = '20261015';
DECLARE @MonthStart date = DATEFROMPARTS(YEAR(@InMonth), MONTH(@InMonth), 1);
DECLARE @MonthEnd date = EOMONTH(@InMonth);
IF @MonthStart < (SELECT MIN(CalendarDate) FROM #Calendar)
OR @MonthEnd > (SELECT MAX(CalendarDate) FROM #Calendar)
THROW 50004, 'The calendar must cover the whole month.', 1;
DECLARE @LastBusinessDate date;
SELECT TOP (1) @LastBusinessDate = CalendarDate
FROM #Calendar
WHERE CalendarDate BETWEEN @MonthStart AND @MonthEnd
AND IsWeekend = 0 AND IsHoliday = 0
ORDER BY CalendarDate DESC;
IF @LastBusinessDate IS NULL
THROW 50005, 'No business day exists under this month''s rules.', 1;
SELECT @LastBusinessDate AS LastBusinessDate;Maintain the Calendar Table as Shared Data
For multiple locations, separate the date dimension from each location's closure schedule. The weekday and fiscal attributes can be shared when their definitions match. Holiday eligibility then belongs to a calendar identifier or location, avoiding one global flag that misstates local operations.
Extend coverage before new-year processing, review observed dates, and validate continuity. Test fiscal boundaries, holiday weekends, and reversed intervals. A calendar table becomes reliable through that maintenance, not merely through generating a long list once.
Use the calendar table in each report and deadline calculation that shares its definition. Then one approved rule supplies the answer, and each query only states its boundary convention. Business-date math gets easier because the difficult assumptions are finally visible.
Related reading on this blog: Find Business Days Between Dates and SQL SERVER 2022: GENERATE_SERIES Function.
A business calendar is not a list of dates, it is an agreed rule for which dates count.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
