Adding Business Hours to a Timestamp in T-SQL

A ticket opened late Friday shouldn't spend its support allowance overnight. Adding business hours means counting time inside approved working windows and skipping everything outside them.

Put Business Hours and Closures in Tables

A due time needs more than a weekday test. Teams close on additional dates and change their operating hours. Those decisions belong in maintained data rather than scattered CASE expressions.

I ask who owns the work calendar before reviewing the deadline query. A correct calculation still returns a wrong deadline from an outdated schedule. Give calendar changes an owner and a review process.

This example stores one daily window and a separate list of closure dates. The working window runs from nine to five. Weekend rows are marked closed instead of relying on session weekday settings.

All timestamps use one agreed local business clock with second precision. This example counts nominal local working time. Convert approved windows to UTC first when the contract requires elapsed time across clock changes.

CREATE TABLE #WorkCalendar
(
    CalendarDate date NOT NULL PRIMARY KEY,
    IsWorking bit NOT NULL,
    OpenTime time(0) NOT NULL,
    CloseTime time(0) NOT NULL,
    CHECK (OpenTime < CloseTime)
);
CREATE TABLE #Closures
(
    CalendarDate date NOT NULL PRIMARY KEY,
    ClosureReason nvarchar(100) NOT NULL
);
INSERT #WorkCalendar VALUES
('2025-01-03', 1, '09:00', '17:00'),
('2025-01-04', 0, '09:00', '17:00'),
('2025-01-05', 0, '09:00', '17:00'),
('2025-01-06', 1, '09:00', '17:00'),
('2025-01-07', 1, '09:00', '17:00'),
('2025-01-08', 1, '09:00', '17:00');
INSERT #Closures VALUES ('2025-01-07', N'Planned business closure');
SELECT c.CalendarDate, c.IsWorking, c.OpenTime, c.CloseTime,
       x.ClosureReason
FROM #WorkCalendar AS c
LEFT JOIN #Closures AS x ON x.CalendarDate = c.CalendarDate
ORDER BY c.CalendarDate;

These dates form a synthetic demonstration calendar. Populate the full reporting horizon for real work. A missing date must be treated as missing configuration, not an inferred working day.

The primary key permits one window per date. Teams with breaks or overnight shifts need an interval table instead. Store several nonoverlapping windows and keep the same accumulation method.

Turn Each Working Day Into an Interval

A calendar date and opening time together define a timestamp. Build the opening and closing values from their parts. This avoids strings whose interpretation depends on language settings.

Exclude weekend rows through IsWorking and closure dates through the separate table. The resulting intervals contain only available working time. No annual closure rules are embedded in the calculation.

CREATE TABLE #WorkWindows
(
    WindowOpen datetime2(0) NOT NULL PRIMARY KEY,
    WindowClose datetime2(0) NOT NULL,
    CHECK (WindowOpen < WindowClose)
);
INSERT #WorkWindows
SELECT DATETIME2FROMPARTS(YEAR(c.CalendarDate), MONTH(c.CalendarDate),
           DAY(c.CalendarDate), DATEPART(hour, c.OpenTime),
           DATEPART(minute, c.OpenTime), DATEPART(second, c.OpenTime), 0, 0),
       DATETIME2FROMPARTS(YEAR(c.CalendarDate), MONTH(c.CalendarDate),
           DAY(c.CalendarDate), DATEPART(hour, c.CloseTime),
           DATEPART(minute, c.CloseTime), DATEPART(second, c.CloseTime), 0, 0)
FROM #WorkCalendar AS c
WHERE c.IsWorking = 1
  AND NOT EXISTS (SELECT 1 FROM #Closures AS x
                  WHERE x.CalendarDate = c.CalendarDate);
SELECT WindowOpen, WindowClose
FROM #WorkWindows ORDER BY WindowOpen;

Check that the intervals don't overlap before using a richer schedule. Overlapping windows count the same second twice. A primary key on opening time alone doesn't prevent that mistake.

An interval's close is the excluded boundary for available work. A deadline can equal that close after consuming the final second. The next positive allowance then resumes in the following working interval.

Keep the original calendar alongside the derived windows. That lets you explain why a date was excluded. A deadline calculation should be traceable to the schedule version used.

Add Business Hours Without a Minute Loop

For each future interval, choose the later of its opening and the ticket time. Discard windows already closed at that ticket time. The remaining interval length is available capacity.

A running sum carries capacity forward across working dates. A second expression subtracts the current window's capacity. Together they identify the interval containing the requested deadline.

DECLARE @TicketAt datetime2(0) = '2025-01-03T15:30:00';
DECLARE @BusinessMinutes int = 240;
IF @TicketAt IS NULL OR @BusinessMinutes IS NULL OR @BusinessMinutes < 0
    THROW 51020, 'A ticket timestamp and nonnegative allowance are required.', 1;
DECLARE @WantedSeconds bigint = CONVERT(bigint, @BusinessMinutes) * 60;
DECLARE @DueAt datetime2(0);
IF @WantedSeconds = 0
    SET @DueAt = @TicketAt;
ELSE
BEGIN
    ;WITH Remaining AS
    (
        SELECT WindowOpen, WindowClose,
            CASE WHEN @TicketAt > WindowOpen THEN @TicketAt
                 ELSE WindowOpen END AS EffectiveOpen
        FROM #WorkWindows WHERE WindowClose > @TicketAt
    ), Capacity AS
    (
        SELECT WindowOpen, EffectiveOpen,
               DATEDIFF_BIG(second, EffectiveOpen, WindowClose) AS SecondsAvailable
        FROM Remaining
    ), Running AS
    (
        SELECT WindowOpen, EffectiveOpen, SecondsAvailable,
            SUM(SecondsAvailable) OVER (ORDER BY WindowOpen
                ROWS UNBOUNDED PRECEDING) AS ThroughSeconds
        FROM Capacity
    )
    SELECT TOP (1) @DueAt = DATEADD(second,
        CONVERT(int, @WantedSeconds - (ThroughSeconds - SecondsAvailable)),
        EffectiveOpen)
    FROM Running
    WHERE ThroughSeconds >= @WantedSeconds
    ORDER BY WindowOpen;
END;
IF @DueAt IS NULL
    THROW 51021, 'The calendar does not contain enough future working time.', 1;
SELECT @TicketAt AS TicketAt, @BusinessMinutes AS AllowedMinutes, @DueAt AS DueAt
INTO #DueCheck;
SELECT TicketAt, AllowedMinutes, DueAt FROM #DueCheck;

The query processes windows, not individual minutes. Seconds preserve a ticket's position within a minute. The final DATEADD adds only the remaining seconds inside one daily window.

That final amount fits an integer for the daily windows shown here. Longer intervals need an explicit maximum and type review. Reject unsupported allowances rather than accepting an overflow or a partial result.

For positive allowances, a ticket outside working time starts consuming at the next opening. Zero returns the supplied timestamp unchanged. State that convention because other support contracts choose a different zero-time behavior.

Check Friday Afternoon Business Hours Against Monday

The synthetic ticket starts Friday at three thirty in the afternoon. Its allowance is four working hours. Friday supplies ninety minutes before the five o'clock close.

The remaining allowance belongs to Monday's opening window. The expected deadline is Monday at eleven thirty. Verify that documented arithmetic against the result produced by the previous block.

DECLARE @Expected datetime2(0) = '2025-01-06T11:30:00';
SELECT @Expected AS ExpectedDeadline;
IF NOT EXISTS (SELECT 1 FROM #DueCheck WHERE DueAt = @Expected)
    THROW 51022, 'The Friday-to-Monday fixture did not match its expected deadline.', 1;
SELECT WindowOpen, WindowClose
FROM #WorkWindows
WHERE WindowOpen >= '2025-01-03T00:00:00'
  AND WindowOpen < '2025-01-07T00:00:00'
ORDER BY WindowOpen;

Don't claim a tested result until you've executed the calculation. The expected value comes from the supplied calendar and allowance. It serves as a checkable fixture rather than an observed production deadline. In my run, the previous block returned Monday, January 6, 2025 at 11:30, and this check passed.

Change the ticket to the weekend and inspect the next working opening. Then test a ticket exactly at closing time. Neither case should consume time inside an interval already closed.

Let Closure Dates Change the Answer

The separate closure table excludes a normally working Tuesday. A ticket whose allowance crosses that date should resume Wednesday. In my test, a Monday ticket at three in the afternoon with four hours came due Wednesday at eleven. Add the closure through data maintenance rather than rewriting SQL.

For business hours, the calendar is part of the business rule. Capture its version when a due time becomes contractual. Later calendar edits shouldn't silently rewrite already accepted deadlines.

I check special closures before blaming the window function. A missing closure is a data problem with an arithmetic-looking symptom. The clock has followed the table's instructions faithfully.

Test a shortened working day by changing that date's stored closing time. Rebuild the intervals before calculating the deadline again. The derived windows must reflect the reviewed schedule.

Reject a Calendar That Ends Too Soon

A schedule without enough future capacity returns no qualifying interval. The query raises an error in that case. It doesn't guess the next weekday or return the final available date.

Validate coverage separately from capacity. A missing row between two populated dates can silently remove work. Require every date in the approved horizon to exist before building windows.

Also validate pause policies before using this for support agreements. Waiting for a customer or another team needs separate state tracking. Working-time arithmetic alone doesn't decide whether the allowance is running.

Preserve an error path for missing dates and unavailable capacity. Alert the calendar owner before the approved horizon expires. A background process should fail visibly instead of inventing availability.

For multiple teams, include a schedule identifier in every interval. Partition the running total by that identifier when calculating several deadlines. Never mix capacity from different support calendars.

Keep the Deadline Rule Explainable

Which business hours and clock does your support agreement promise? Write that answer beside the calendar's ownership and update process. Store the calculated deadline with the input allowance and schedule reference.

Keep the loop-free query as a calculation over approved intervals. Extend the calendar before its horizon expires. A calendar that stops Monday has limited ambitions.

Related reading on this blog: A Calendar Table With Holidays for Date Math and Weekday Logic That Works Under Any DATEFIRST Setting.

A working-time deadline is not a plain elapsed-hour addition, it is an allowance consumed inside approved intervals.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.