Check if a datetime range overlaps business hours on specific days of the week in T-SQL
I have an SQL Server table Meeting with start and end times stored as a datetime. How can I retrieve records where the datetime range overlaps with company business hours. (IE: Monday to Friday, 9am to 5pm).
So for example, if I had the following records:
Id StartDateTime EndDateTime
1 2025-03-24 08:00 2025-03-24 10:00 -- Monday 8am to 10am
2 2025-03-26 17:00 2025-03-26 19:00 -- Wednesday 5pm to 7pm
3 2025-03-27 16:00 2025-03-27 18:00 -- Thursday 4pm to 6pm
4 2025-03-28 07:00 2025-03-28 20:00 -- Friday 7am to 8pm
5 2025-03-30 11:00 2025-03-30 14:00 -- Sunday 11am to 2pm
6 2025-04-03 19:00 2025-04-04 08:00 -- Thursday 7pm to Friday 8am
7 2025-04-04 17:00 2025-04-07 09:00 -- Friday 5pm to Monday 9am
8 2025-04-05 08:00 2025-04-06 08:00 -- Saturday 8am to Sunday 8am
9 2025-04-05 08:00 2025-04-12 08:00 -- Saturday 8am to (next) Saturday 8am
10 2025-04-06 08:00 2025-04-12 08:00 -- Sunday 8am to (next) Saturday 8am
11 2025-04-08 20:00 2025-04-10 08:00 -- Thursday 8pm to Saturday 8am
I would want the following results:
Id StartDateTime EndDateTime
1 2025-03-24 08:00 2025-03-24 10:00
3 2025-03-27 16:00 2025-03-27 18:00
4 2025-03-28 07:00 2025-03-28 20:00
9 2025-04-05 08:00 2025-04-12 08:00
10 2025-04-06 08:00 2025-04-12 08:00
11 2025-04-08 20:00 2025-04-10 08:00
I can cover single-day timespans by checking the day of the week and comparing the time ranges, but this doesn't account for time ranges that span multiple days.
SET DATEFIRST 7
SELECT
Id,
StartDateTime,
EndDateTime
FROM Meeting
WHERE
-- Weekday
DATEPART(weekday, StartDateTime) BETWEEN 2 AND 6
-- Within business hours
AND CAST(StartDateTime AS TIME) < '17:00:00'
AND CAST(EndDateTime AS TIME) > '09:00:00'
-- Returns Ids 1,3,4, but not 9,10,11
How do I detect if a date range overlaps with business hours for specific days of the week?
Answer
The CTE (Dates) generates each individual date within the meeting range. For each of those days, it creates a business hours range from 09:00–17:00. Then EndDateTime > BusinessStart AND StartDateTime < BusinessEnd checks if the meeting time overlaps with any of those business hour ranges. The outer query will then only select the distinct matching meetings.
WITH Dates AS (
SELECT
m.Id,
m.StartDateTime,
m.EndDateTime,
d.DayDate,
CAST(DATEADD(HOUR, 9, d.DayDate) AS DATETIME) AS BusinessStart,
CAST(DATEADD(HOUR, 17, d.DayDate) AS DATETIME) AS BusinessEnd
FROM
Meeting m
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, m.StartDateTime, m.EndDateTime) + 1)
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, CAST(m.StartDateTime AS DATE)) AS DayDate
FROM master.dbo.spt_values -- a trick to generate numbers
) d
WHERE
DATEPART(WEEKDAY, d.DayDate) BETWEEN 2 AND 6 -- Monday to Friday
)
SELECT DISTINCT
Id, StartDateTime, EndDateTime
FROM Dates
WHERE
EndDateTime > BusinessStart AND StartDateTime < BusinessEnd