如何编写SQL查询获取早于报表运行日及下月到期的合同

假设你的合同表名为contracts，存储到期日期的字段为contract_end_date，以下是针对不同数据库的实现方案，完全匹配你的需求：

核心筛选逻辑 #

需要同时筛选两类合同：

• 所有到期日早于报表运行当天的已到期合同
• 到期日属于报表运行日期所在月份的下一个自然月的合同（比如9月1日运行时，匹配10月全月到期的合同）

MySQL 实现 #

SELECT *
FROM contracts
WHERE 
  -- 已到期合同：到期日早于今天
  contract_end_date < CURDATE()
  OR
  -- 下月到期合同：匹配下一个月的所有日期
  DATE_FORMAT(contract_end_date, '%Y-%m') = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');

SQL Server 实现 #

SELECT *
FROM contracts
WHERE 
  -- 已到期合同：剔除时间部分，只比较日期
  contract_end_date < CAST(GETDATE() AS DATE)
  OR
  -- 下月到期合同：锁定下月的起止范围，避免日期格式问题
  contract_end_date >= DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND contract_end_date < DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));

PostgreSQL 实现 #

SELECT *
FROM contracts
WHERE 
  -- 已到期合同：早于当前日期
  contract_end_date < CURRENT_DATE
  OR
  -- 下月到期合同：匹配下一月的年月标识
  TO_CHAR(contract_end_date, 'YYYY-MM') = TO_CHAR(CURRENT_DATE + INTERVAL '1 month', 'YYYY-MM');

关键细节说明 #

• 所有查询都优先使用日期而非带时间的字段比较，避免因2024-09-01 00:00:01这类时间值导致的筛选错误
• 如果你的contract_end_date包含时间戳，建议先转换为纯日期格式（比如CAST(contract_end_date AS DATE)）后再进行比较
• 可以根据业务需求追加其他条件（比如仅筛选有效状态的合同），直接添加到WHERE子句即可

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