SQL Server如何判断数据表是否存在指定日期范围内的记录

需要在SQL Server的MYTABLE表中，查询所有与传入日期范围存在时间重叠的记录。
测试表数据如下：

DISPLAY_START_DATE      DISPLAY_END_DATE
2022-02-02 00:00:00.000     2022-02-28 00:00:00.000
2022-02-02 00:00:00.000     2022-02-06 10:34:01.653
2022-02-01 00:00:00.000     2022-02-17 00:00:00.000
2022-02-07 00:00:00.000     2022-02-25 00:00:00.000

原有查询通过拼接多个OR条件判断重叠，传入查询范围@startdate = '2022-02-01'、@enddate = '2022-02-10'时，仅返回1条记录，存在明显漏匹配：

DISPLAY_START_DATE      DISPLAY_END_DATE
2022-02-02 00:00:00.000     2022-02-06 10:34:01.653

原有存在漏洞的查询语句如下：

DECLARE @startdate AS datetime ='2022-02-01' 
DECLARE @enddate AS datetime ='2022-02-10'
SELECT * from MYTABLE mt
WHERE 
(mt.DISPLAY_START_DATE = @startdate and  mt.DISPLAY_END_DATE = @enddate)    OR
(mt.DISPLAY_START_DATE < @startdate and  mt.DISPLAY_END_DATE > @enddate)    OR
(mt.DISPLAY_START_DATE < @startdate and  mt.DISPLAY_END_DATE < @enddate)    OR
(mt.DISPLAY_START_DATE < @startdate and  mt.DISPLAY_END_DATE < @enddate and  
    mt.DISPLAY_END_DATE > @startdate)   OR
(mt.DISPLAY_START_DATE > @startdate and  mt.DISPLAY_END_DATE < @enddate) OR
(mt.DISPLAY_START_DATE > @startdate and mt.DISPLAY_START_DATE < @enddate and 
    mt.DISPLAY_END_DATE < @enddate)

手动枚举所有重叠场景的写法极易出现逻辑漏洞：

1. 存在条件遗漏：没有覆盖「记录开始时间落在查询区间内、记录结束时间晚于查询结束时间」这类重叠场景，比如样例中2022-02-07到2022-02-25的记录就被漏掉
2. 存在错误条件：第三个OR分支(mt.DISPLAY_START_DATE < @startdate and mt.DISPLAY_END_DATE < @enddate)会误判完全不重叠的记录（比如结束时间早于查询开始时间的记录）
3. 存在冗余条件：多个OR分支的判定范围互相包含，没有存在必要，维护性极差

判断两个闭区间[记录开始, 记录结束]和[查询开始, 查询结束]存在重叠，不需要枚举所有场景，直接使用通用判定规则即可：

两个时间区间存在重叠的充要条件是：一个区间的开始时间早于另一个区间的结束时间，且该区间的结束时间晚于另一个区间的开始时间。

修正后的查询语句：

DECLARE @startdate AS datetime ='2022-02-01' 
DECLARE @enddate AS datetime ='2022-02-10'

SELECT * FROM MYTABLE mt
WHERE mt.DISPLAY_START_DATE < @enddate
  AND mt.DISPLAY_END_DATE > @startdate

这个条件可以覆盖所有重叠场景：

• 记录区间与查询区间完全相等
• 记录区间完全包含查询区间
• 查询区间完全包含记录区间
• 记录区间尾部与查询区间头部重叠
• 记录区间头部与查询区间尾部重叠

执行上述语句会返回所有4条符合重叠要求的记录，没有漏匹配、误匹配：

DISPLAY_START_DATE      DISPLAY_END_DATE
2022-02-02 00:00:00.000     2022-02-28 00:00:00.000
2022-02-02 00:00:00.000     2022-02-06 10:34:01.653
2022-02-01 00:00:00.000     2022-02-17 00:00:00.000
2022-02-07 00:00:00.000     2022-02-25 00:00:00.000

如果业务规则认为「区间端点相等属于重叠」（比如记录结束时间恰好等于查询开始时间算有交集），只需要把条件中的<和>替换为<=和>=即可。

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