Microsoft SQL Server日期范围冲突校验问题求助

需求回顾 #

新增PreApprovedExpense记录时，需禁止新记录的日期范围与已有记录重叠，仅允许新记录的结束日期等于已有记录的开始日期（该情况不算重叠）。

核心冲突判断逻辑 #

两个日期区间[新Start, 新Finish]与[已有Start, 已有Finish]，冲突的判定条件为：

• 两个区间存在重叠（即新区间的开始早于已有区间的结束，且新区间的结束晚于已有区间的开始）
• 且不属于「新记录结束日期等于已有记录开始日期」的例外情况

修正后的SQL代码 #

DECLARE @UserId nvarchar(max) = '26046958'
DECLARE @CompanyCode nvarchar(max) = '0601'
DECLARE @StartDate date = '2022-11-07'
DECLARE @FinishDate date = '2022-11-12'

-- 检查是否存在冲突记录
SELECT * 
FROM PreApprovedExpense WITH(NOLOCK)
WHERE UserId = @UserId
  AND CompanyCode = @CompanyCode
  -- 排除允许的例外场景
  AND NOT (@FinishDate = StartDate)
  -- 判断区间是否重叠
  AND @StartDate < FinishDate 
  AND @FinishDate > StartDate

代码说明 #

1. 统一日期类型：将变量和表字段统一转为date类型，避免datetime的时间部分干扰判断（若表中字段为datetime，可改为CAST(StartDate AS date)、CAST(FinishDate AS date)）。
2. 重叠判断：@StartDate < FinishDate和@FinishDate > StartDate组合，能精准覆盖所有区间重叠的场景（包含部分重叠、完全覆盖的情况）。
3. 例外排除：NOT (@FinishDate = StartDate)专门排除允许的「新结束日期等于已有开始日期」的情况。

验证示例 #

• 已有记录：StartDate='2022-11-10'、FinishDate='2022-11-15'

• 新增记录：StartDate='2022-11-07'、FinishDate='2022-11-12'
  此时@StartDate (2022-11-07) < FinishDate (2022-11-15)且@FinishDate (2022-11-12) > StartDate (2022-11-10)，同时@FinishDate != StartDate，查询会返回冲突记录，禁止创建。

• 新增记录：StartDate='2022-11-05'、FinishDate='2022-11-10'
  此时@FinishDate = StartDate（已有记录的StartDate），触发例外排除条件，查询无结果，允许创建。

内容的提问来源于stack exchange，提问作者Ufuk Çiçek