SQLite跨年度日期范围查询无结果问题求助

核心原因 #

SQLite对字符串类型的日期采用字典序比较，你使用的MM-DD-YYYY格式下，字符串'11-01-2023'的首字符是'1'，而'05-21-2024'的首字符是'0'，所以'11-01-2023' > '05-21-2024'。你的跨年度查询条件transdate >= '11-01-2023' AND transdate <= '05-21-2024'等价于找一个同时大于等于大值、小于等于小值的记录，自然无结果返回。

而2024年的查询中，'01-01-2024'到'05-21-2024'的字符串首字符都是'0'，后续的月份、日期顺序和日期逻辑一致，所以能正常返回数据。

解决方案 #

1. 临时修复：用日期函数转换格式后比较

使用SQLite的date()函数将原MM-DD-YYYY格式的字符串转换为标准的YYYY-MM-DD格式（该格式的字符串比较与日期逻辑一致），再执行查询：

SELECT trans.date as 'transdate' 
FROM 'Transaction' trans 
WHERE date(trans.date, '%m-%d-%Y') >= '2023-11-01' 
  AND date(trans.date, '%m-%d-%Y') <= '2024-05-21'

注：date()函数的第二个参数'%m-%d-%Y'用于指定输入字符串的格式，确保转换准确。

2. 长期优化：修改表结构存储标准日期格式

如果业务允许，建议修改表结构，将date列改为DATE类型，或者直接存储YYYY-MM-DD格式的字符串。这样后续查询无需每次转换，既提升效率，也能从根源避免此类日期比较问题。

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