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 Read more, 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