如何编写SQL查询筛选包含指定年月的日期区间数据

你的原始SQL逻辑存在错误，它只会筛选出「合同的起始年月晚于等于指定年月，且结束年月早于等于指定年月」的数据——也就是完全被包含在指定年月内的合同，但这和你的需求不符。你需要的是指定年月与合同时间区间存在重叠的记录，只要指定年月的任何一天落在合同的StartDate到EndDate之间，就应该被筛选出来。

正确的SQL思路 #

核心逻辑是：判断指定年月的第一天是否小于等于合同的EndDate，同时指定年月的最后一天是否大于等于合同的StartDate。只要满足这两个条件，就说明该年月与合同区间有交集。

针对不同数据库的实现示例 #

1. SQL Server

SELECT *
FROM contracts
WHERE 
    -- 合同开始日期不晚于指定年月的最后一天
    StartDate <= EOMONTH(DATEFROMPARTS(@year, @month, 1))
    -- 合同结束日期不早于指定年月的第一天
    AND EndDate >= DATEFROMPARTS(@year, @month, 1)

2. MySQL

SELECT *
FROM contracts
WHERE 
    StartDate <= LAST_DAY(STR_TO_DATE(CONCAT(@year, '-', @month, '-01'), '%Y-%m-%d'))
    AND EndDate >= STR_TO_DATE(CONCAT(@year, '-', @month, '-01'), '%Y-%m-%d')

3. PostgreSQL

SELECT *
FROM contracts
WHERE 
    StartDate <= (DATE_TRUNC('month', MAKE_DATE(@year, @month, 1)) + INTERVAL '1 month - 1 day')::DATE
    AND EndDate >= MAKE_DATE(@year, @month, 1)

为什么你的原始SQL不对？ #

拿你给出的示例07/2022来说：

• 对于id=1的合同，year(startdate)=2021小于@year=2022，所以year(startdate)>=@year不成立，直接被过滤，这显然不符合你的期望。
• 正确的逻辑会判断2022-07-01 <= 2022-09-24，且2022-07-31 >= 2021-03-01，条件成立，所以会被选中。

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