如何在T-SQL中检测时间范围是否与工作日营业时间重叠

我有一个SQL Server的Meeting表，其中StartDateTime和EndDateTime字段为datetime类型。需要查询出时间范围与公司营业时间（周一至周五9:00-17:00）存在重叠的会议记录。

示例数据 #

Id   StartDateTime      EndDateTime
1    2025-03-24 08:00   2025-03-24 10:00    -- 周一 8点到10点
2    2025-03-26 17:00   2025-03-26 19:00    -- 周三 17点到19点
3    2025-03-27 16:00   2025-03-27 18:00    -- 周四 16点到18点
4    2025-03-28 07:00   2025-03-28 20:00    -- 周五 7点到20点
5    2025-03-30 11:00   2025-03-30 14:00    -- 周日 11点到14点
6    2025-04-03 19:00   2025-04-04 08:00    -- 周四 19点到周五8点
7    2025-04-04 17:00   2025-04-07 09:00    -- 周五17点到周一9点
8    2025-04-05 08:00   2025-04-06 08:00    -- 周六8点到周日8点
9    2025-04-05 08:00   2025-04-12 08:00    -- 周六8点到下周六8点
10   2025-04-06 08:00   2025-04-12 08:00    -- 周日8点到周六8点
11   2025-04-08 20:00   2025-04-10 08:00    -- 周四20点到周六8点

期望结果 #

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

现有问题 #

我原本的查询只能处理单日的情况，无法覆盖跨多天的会议，比如ID9、10、11这类记录。现有查询如下：

SET DATEFIRST 7
SELECT
    Id,
    StartDateTime,
    EndDateTime
FROM Meeting
WHERE
     -- 工作日判断
    DATEPART(weekday, StartDateTime) BETWEEN 2 AND 6 
    -- 时间范围与营业时间重叠
    AND CAST(StartDateTime AS TIME) < '17:00:00'
    AND CAST(EndDateTime AS TIME) > '09:00:00'
-- 仅返回Ids 1,3,4，漏掉了9,10,11

要处理跨多天的情况，核心是判断会议的时间范围是否与至少一个工作日的营业时间（9:00-17:00）存在重叠。可以通过以下两种方法实现：

方法一：利用日期生成与 EXISTS 子查询 #

通过生成会议开始到结束之间的所有日期，筛选出其中的工作日，再检查该工作日的营业时间是否与会议时间重叠：

SET DATEFIRST 7;

WITH DateRange AS (
    -- 生成当前会议覆盖的所有日期（按天）
    SELECT DATEADD(day, number, CAST(m.StartDateTime AS DATE)) AS BusinessDate
    FROM master.dbo.spt_values 
    WHERE type = 'P' 
      AND number <= DATEDIFF(day, m.StartDateTime, m.EndDateTime)
)
SELECT 
    m.Id,
    m.StartDateTime,
    m.EndDateTime
FROM Meeting m
WHERE EXISTS (
    SELECT 1
    FROM DateRange dr
    -- 判断当前日期为工作日
    WHERE DATEPART(weekday, dr.BusinessDate) BETWEEN 2 AND 6
    -- 判断会议时间与该工作日营业时间重叠
    AND m.EndDateTime > DATEADD(hour, 9, dr.BusinessDate)
    AND m.StartDateTime < DATEADD(hour, 17, dr.BusinessDate)
);

方法二：简化的时间范围重叠判断 #

无需生成所有日期，直接通过逻辑判断覆盖所有重叠场景：

SET DATEFIRST 7;

SELECT
    Id,
    StartDateTime,
    EndDateTime
FROM Meeting
WHERE
    -- 场景1：单日会议，与营业时间重叠
    (
        DATEPART(weekday, StartDateTime) BETWEEN 2 AND 6
        AND CAST(StartDateTime AS TIME) < '17:00:00'
        AND CAST(EndDateTime AS TIME) > '09:00:00'
    )
    -- 场景2：跨天会议，覆盖至少一个完整工作日
    OR (
        DATEDIFF(day, StartDateTime, EndDateTime) >= 1
        AND (
            -- 会议开始于工作日，且结束时间晚于当日下班时间
            (DATEPART(weekday, StartDateTime) BETWEEN 2 AND 6 
             AND m.EndDateTime > DATEADD(hour,17,CAST(m.StartDateTime AS DATE)))
            -- 会议结束于工作日，且开始时间早于当日上班时间
            OR (DATEPART(weekday, EndDateTime) BETWEEN 2 AND 6 
                AND m.StartDateTime < DATEADD(hour,9,CAST(m.EndDateTime AS DATE)))
            -- 会议覆盖了完整的工作日（比如从周六到下周五）
            OR DATEDIFF(day, StartDateTime, EndDateTime) >= 2
        )
    );

说明 #

两种方法都能正确覆盖所有测试场景：

• 方法一逻辑直观，适合理解和维护；
• 方法二减少了日期生成的开销，性能更优。

测试后两种方法都能返回你期望的结果（Ids 1,3,4,9,10,11）。

内容的提问来源于stack exchange，提问作者Stevoisiak