如何在SQL Server中检查含可空datetime2的促销时间重叠？

核心逻辑分析 #

要判断两个促销活动是否重叠，需同时满足两个条件：

1. 两个促销当前均处于有效状态
2. 两者的有效时间范围存在重叠

促销有效状态判断 #

• 促销有效 = (开始日期为NULL 或 开始日期≤当前时间) 且 (结束日期为NULL 或 结束日期≥当前时间)
• 无效场景：
  • 开始日期>当前时间：促销未生效
  • 结束日期<当前时间：促销已过期

时间范围重叠判断 #

将NULL日期替换为SQL Server支持的极值，统一处理所有场景：

• 开始日期为NULL → 替换为'1900-01-01'（最早datetime2日期）
• 结束日期为NULL → 替换为'9999-12-31'（最晚datetime2日期）

两个时间段[SA, EA]和[SB, EB]重叠的条件为：
ISNULL(SA, '1900-01-01') < ISNULL(EB, '9999-12-31') 且 ISNULL(SB, '1900-01-01') < ISNULL(EA, '9999-12-31')

具体SQL语句 #

场景1：添加新促销时，检查与当前产品的现有促销是否重叠 #

假设新促销参数为@ProductId（产品ID）、@NewStart（新促销开始日期）、@NewEnd（新促销结束日期），促销表名为Promotions，包含字段PromotionId、ProductId、StartDate、EndDate：

SELECT PromotionId, StartDate, EndDate
FROM Promotions
WHERE ProductId = @ProductId
  -- 排除新促销本身（修改场景可改为PromotionId != @CurrentPromotionId）
  AND PromotionId != @NewPromotionId
  -- 新促销处于有效状态
  AND ((@NewStart IS NULL OR @NewStart <= GETDATE()) AND (@NewEnd IS NULL OR @NewEnd >= GETDATE()))
  -- 现有促销处于有效状态
  AND ((StartDate IS NULL OR StartDate <= GETDATE()) AND (EndDate IS NULL OR EndDate >= GETDATE()))
  -- 时间范围重叠
  AND (ISNULL(@NewStart, '1900-01-01') < ISNULL(EndDate, '9999-12-31'))
  AND (ISNULL(StartDate, '1900-01-01') < ISNULL(@NewEnd, '9999-12-31'))

场景2：查询同一产品下所有互相重叠的促销对 #

使用自连接避免重复配对（仅返回p1.PromotionId < p2.PromotionId的组合）：

SELECT 
  p1.PromotionId AS 促销ID1,
  p1.StartDate AS 促销1开始日期,
  p1.EndDate AS 促销1结束日期,
  p2.PromotionId AS 促销ID2,
  p2.StartDate AS 促销2开始日期,
  p2.EndDate AS 促销2结束日期
FROM Promotions p1
JOIN Promotions p2 
  ON p1.ProductId = p2.ProductId
  AND p1.PromotionId < p2.PromotionId
WHERE 
  -- 促销1有效
  ((p1.StartDate IS NULL OR p1.StartDate <= GETDATE()) AND (p1.EndDate IS NULL OR p1.EndDate >= GETDATE()))
  -- 促销2有效
  AND ((p2.StartDate IS NULL OR p2.StartDate <= GETDATE()) AND (p2.EndDate IS NULL OR p2.EndDate >= GETDATE()))
  -- 时间范围重叠
  AND (ISNULL(p1.StartDate, '1900-01-01') < ISNULL(p2.EndDate, '9999-12-31'))
  AND (ISNULL(p2.StartDate, '1900-01-01') < ISNULL(p1.EndDate, '9999-12-31'))

说明 #

• 覆盖所有NULL日期场景：包括开始/结束日期单独为NULL、两者均为NULL的情况
• 基于当前时间判断促销有效性，只检测正在生效或未过期的促销重叠
• 使用极值日期处理NULL，避免逻辑漏洞

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