Articles

SQL Business-Day Interview Questions: Holidays, Time Zones, and Service Deadlines

Prepare for SQL business-day interviews with regional calendars, exact cutoffs, UTC conversion, business ordinals, DST deadlines, and missing-calendar checks.

“Add two business days” is incomplete until you know which calendar applies, whether the receipt date counts, and what happens at the cutoff. SQL business-day interview questions become much easier when you separate those decisions from the date arithmetic.

Official facts below come from Microsoft SQL Server documentation, checked on October 1, 2026. Original examples and engineering recommendations define a fictional service policy; they are not statutory deadlines or Microsoft business rules. No candidate reports or employer question-frequency claims are used. The SQL examples target SQL Server 2016 or later, rather than claiming identical syntax across databases.

What does “two business days” actually promise?

Ask for the service region, holiday calendar, acceptance cutoff, counting convention, and deadline time. “Monday through Friday” may be a starting assumption, but it does not identify a bank holiday, observed holiday, local closure, or special working Saturday.

Original contract used throughout this guide:

The service uses a versioned West-region calendar and the Pacific time zone.
A request arriving on an open date strictly before 17:00 is accepted that date.
Arrival at 17:00 or later, or on a closed date, is accepted on the next open date.
The accepted date is day zero. A two-business-day promise ends on the second following open date, at 17:00 local time.
A nonnegative integer N generalizes the promise. For N = 0, the deadline is 17:00 on the accepted date.

There is deliberately no opening-time gate in this simplified policy. If the service accepts only during an operating interval, add that requirement explicitly. Do not invent it halfway through the query.

An inclusive convention would produce different answers. Repeat the contract back to the interviewer before writing SQL: “I will find the accepted business date, advance its business ordinal by N, then attach the due date's local closing time.” That sentence exposes the off-by-one decision.

Why don't DATEADD and weekday filters solve the whole problem?

Official fact: DATEDIFF counts specified datepart boundaries. Crossing midnight can count as one day even when very little time elapsed. It does not consult a service holiday calendar. DATEADD advances a specified datepart; selecting weekday does not turn it into a holiday-aware business-day function. DATEDIFF, DATEADD

Official fact: DATEPART(weekday, ...) depends on SET DATEFIRST. A predicate that assumes Saturday is always a particular integer can change meaning with the session setting. DATEPART, SET DATEFIRST

Engineering recommendation: represent the approved policy as data:

ServiceCalendar(
  region_id, calendar_version, calendar_date,
  is_business_day, business_rank, cutoff_local, close_local
)

Use one row per calendar date, region, and version, including closed dates. Assign dense increasing business_rank values only to open dates; closed rows have no business rank. Store cutoff and close values as time, dates as date, and the region's zone identifier separately.

A calendar build can number the filtered open rows with ROW_NUMBER() partitioned by region and version, ordered by the unique calendar date. Numbering all dates before filtering would leave gaps and break rank addition. ROW_NUMBER

Observed holidays belong in the published calendar once. Joining several holiday records directly onto a date can multiply rows; an exclusion list should not accidentally make a deadline appear twice.

When should you convert UTC into the service time zone?

Convert the receipt instant first, then extract the local date and local time. Casting the UTC timestamp to date first can select Saturday when the service still considers it Friday.

Official fact: SQL Server's AT TIME ZONE converts a datetimeoffset input to the target zone using its conversion rules. An offset-free input instead receives the target zone's offset as a local wall time. Those are different operations. AT TIME ZONE

For this original example, the receipt value carries an explicit UTC offset:

DECLARE @received_at datetimeoffset(7) =
    '2026-03-07T00:59:59+00:00';
DECLARE @zone sysname = 'Pacific Standard Time';

SELECT @received_at AT TIME ZONE @zone AS received_local;

The corresponding Pacific local receipt is Friday, March 6, at 16:59:59. The Windows zone name Pacific Standard Time includes applicable daylight-saving rules; it is not an instruction to use a fixed minus-eight-hour offset forever.

If an existing datetime2 column is contractually UTC, attach UTC before converting: received_at AT TIME ZONE 'UTC' AT TIME ZONE @zone. Do not attach the service zone directly to that UTC wall-clock value.

A datetimeoffset stores an offset, not the service's geographical zone identity. Keep the zone name needed for future local deadlines. Validate it against the environment's supported zone list. datetimeoffset, sys.time_zone_info

How do you select the accepted date and advance business ordinals?

The following original T-SQL query illustrates one request. Parameters include @region, @calendar_version, @N, and the receipt and zone above. It assumes non-null validated inputs, an approved calendar, and a validated contiguous calendar horizon. Coverage checks are discussed immediately afterward.

WITH LocalReceipt AS (
    SELECT @received_at AT TIME ZONE @zone AS local_at
), ReceiptParts AS (
    SELECT CAST(local_at AS date) AS local_date,
           CAST(local_at AS time(7)) AS local_time
    FROM LocalReceipt
)
SELECT r.local_date,
       a.calendar_date AS accepted_date,
       due.calendar_date AS due_date,
       due.close_local,
       CASE
         WHEN @N < 0 THEN 'INVALID_N'
         WHEN today.calendar_date IS NULL THEN 'MISSING_RECEIPT_DATE'
         WHEN a.calendar_date IS NULL THEN 'NO_ACCEPTANCE_DATE'
         WHEN due.calendar_date IS NULL THEN 'NO_DUE_DATE'
         ELSE 'OK'
       END AS calculation_status
FROM ReceiptParts AS r
LEFT JOIN dbo.ServiceCalendar AS today
  ON today.region_id = @region
 AND today.calendar_version = @calendar_version
 AND today.calendar_date = r.local_date
OUTER APPLY (
    SELECT TOP (1) c.calendar_date, c.business_rank
    FROM dbo.ServiceCalendar AS c
    WHERE today.calendar_date IS NOT NULL
      AND c.region_id = @region
      AND c.calendar_version = @calendar_version
      AND c.is_business_day = 1
      AND (
        c.calendar_date > r.local_date
        OR (c.calendar_date = r.local_date
            AND r.local_time < c.cutoff_local)
      )
    ORDER BY c.calendar_date
) AS a
LEFT JOIN dbo.ServiceCalendar AS due
  ON due.region_id = @region
 AND due.calendar_version = @calendar_version
 AND due.is_business_day = 1
 AND due.business_rank = a.business_rank + @N;

The anchor lookup uses an ordered TOP (1). An unordered first row is not a meaningful “next business date.” OUTER APPLY and the left join preserve the request when no matching calendar row exists, allowing an error status instead of a disappearing request. TOP, FROM and APPLY

Use the result only when its status is OK. In a multi-request version, carry the request ID through normalization and require exactly one result per input request. Do not turn null deadlines into “on time” through a comparison that evaluates to unknown.

Enforce unique (region_id, calendar_version, calendar_date) keys and unique business ranks within each open-date partition. A filtered unique index can enforce rank uniqueness for open rows without treating all closed rows' null ranks as valid duplicates. Actual index choices still need a representative execution plan. Filtered indexes

Which boundary cases expose the wrong answer?

Original fixture: March 9, 2026 is a synthetic West-service closure, not a claimed national holiday. Saturdays and Sundays are closed; March 10–12 are open. All shown requests use the same two-following-business-day policy.

Pacific local receipt	Accepted day zero	Following open dates	Due locally
Fri Mar 6, 16:59:59	Fri Mar 6	Tue Mar 10, Wed Mar 11	Wed Mar 11, 17:00
Fri Mar 6, exactly 17:00	Tue Mar 10	Wed Mar 11, Thu Mar 12	Thu Mar 12, 17:00
Sat Mar 7, 10:00	Tue Mar 10	Wed Mar 11, Thu Mar 12	Thu Mar 12, 17:00
Mon Mar 9, 09:00, service closed	Tue Mar 10	Wed Mar 11, Thu Mar 12	Thu Mar 12, 17:00

Change only the region's calendar so Monday is open: the first row's due date becomes Tuesday, March 10. This demonstrates why the holiday ownership belongs in the join keys, rather than in a universal weekend formula.

The March example also crosses a Pacific daylight-saving transition. Build the deadline's local wall time from its due date and close_local, then apply the zone on that target date:

-- Only after an OK calculation, with non-null date/time.
DECLARE @due_wall datetime2(0) = DATETIME2FROMPARTS(
    YEAR(@due_date), MONTH(@due_date), DAY(@due_date),
    DATEPART(hour, @close_local), DATEPART(minute, @close_local),
    DATEPART(second, @close_local), 0, 0);

SELECT @due_wall AT TIME ZONE @zone AT TIME ZONE 'UTC'
    AS due_at_utc;

This fragment assumes whole-second closing times. The first row's Wednesday 17:00 Pacific deadline becomes Thursday, March 12, 00:00 UTC. Reusing the receipt's minus-eight-hour offset would make it an hour late. DATETIME2FROMPARTS

For deadlines near a daylight-saving gap or repeated local hour, define a business policy explicitly. Microsoft documents specific handling for those wall times and classifies AT TIME ZONE as nondeterministic because external rules can change. A database's default resolution is not automatically the service's intended promise.

How do missing calendars and later policy changes affect correctness?

A missing row is different from an explicitly closed date. Validate that the calendar contains every date through the supported calculation horizon and that open-date ranks are dense and consistent. The receipt-date join above detects one missing boundary; it does not certify that every intervening date exists.

If a Wednesday open row is accidentally missing, a “next available row” approach may silently skip it. Reject the broken calendar before calculation. If the calendar merely ends before the required due date, return a coverage error and extend the approved horizon; do not substitute the last known open date.

Engineering recommendation: version calendar publications and retain the receipt instant, region, zone, calendar version, acceptance date, due date, policy version, and resulting UTC deadline. Validate a new version before switching new requests to it.

If a later emergency closure changes the calendar, the product owner must decide whether existing promises are grandfathered or explicitly recalculated. Persist revised results and their reason. Quietly joining old requests to the newest holiday table makes historical SLA explanations unstable.