如何获取日期区间内的实体？日期范围查询结果异常求助

问题根源 #

你的expiration字段是字符串类型（如VARCHAR/TEXT），而非数据库原生的日期类型（DATE/DATETIME）。数据库执行条件判断时，会按字符串字典序而非实际日期逻辑进行比较：

• 字符串对比中，05-05-2022与09-01-2022逐字符匹配，前两位05 < 09，因此被判定为符合expiration <: '09-01-2022'的条件。
• 但按你期望的日期逻辑（假设格式为DD-MM-YYYY），05-05-2022是5月5日，晚于09-01-2022（1月9日），本应被排除，这就导致结果不符合预期。

解决方案 #

方案1：修改字段为日期类型（推荐）

将expiration字段改为DATE类型，数据库会自动按日期逻辑处理比较，无需调整查询语句。
以MySQL为例，执行以下SQL：

-- 先将现有字符串日期转换为标准日期格式（若格式为DD-MM-YYYY）
UPDATE your_table SET expiration = STR_TO_DATE(expiration, '%d-%m-%Y');
-- 修改字段类型为DATE
ALTER TABLE your_table MODIFY COLUMN expiration DATE;

方案2：查询时转换字符串为日期

若无法修改字段类型，在查询中把字符串转换为日期后再做比较，不同数据库的转换函数如下：

• MySQL：

WHERE STR_TO_DATE(expiration, '%d-%m-%Y') > '2022-01-01' 
  AND STR_TO_DATE(expiration, '%d-%m-%Y') < '2022-01-09'

• PostgreSQL：

WHERE TO_DATE(expiration, 'DD-MM-YYYY') > '2022-01-01'
  AND TO_DATE(expiration, 'DD-MM-YYYY') < '2022-01-09'

• SQL Server：

WHERE CONVERT(DATE, expiration, 105) > '2022-01-01'
  AND CONVERT(DATE, expiration, 105) < '2022-01-09'

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