Oracle中如何按日期时间筛选数据？当前查询结果不符合预期

你错误地将日期类型字段CREATE_DATE转换成字符串后再进行范围比较。字符串比较是按字符顺序逐位对比的，而你使用的DD/MM/YYYY hh24:mi:ss格式会先比较日部分，再比较月、年，这种逻辑和日期的实际时间顺序不一致，导致筛选结果不符合预期。另外，对字段使用函数还会导致该字段上的索引无法被利用，降低查询效率。

有两种可行的修正方式：

方式1：直接用日期类型对比（推荐） #

将查询条件里的字符串转成日期类型，直接和原日期字段比较，这样既保证逻辑正确，又能利用字段索引提升性能：

SELECT COLUMN1, TO_CHAR(CREATE_DATE, 'DD/MM/YYYY hh24:mi:ss') create_date
FROM T_TEST_DATA 
WHERE CREATE_DATE >= TO_DATE('20/03/2024 22:57:44', 'DD/MM/YYYY hh24:mi:ss') 
  AND CREATE_DATE <= TO_DATE('21/03/2024 08:42:53', 'DD/MM/YYYY hh24:mi:ss')
ORDER BY CREATE_DATE DESC

方式2：调整字符串格式后对比（不推荐） #

如果必须用字符串比较，改用YYYY/MM/DD hh24:mi:ss格式，这种格式的字符串顺序和日期时间顺序一致，能保证比较逻辑正确，但依然会失去索引优势：

SELECT COLUMN1, TO_CHAR(CREATE_DATE, 'DD/MM/YYYY hh24:mi:ss') create_date
FROM T_TEST_DATA 
WHERE TO_CHAR(CREATE_DATE, 'YYYY/MM/DD hh24:mi:ss') >= '2024/03/20 22:57:44' 
  AND TO_CHAR(CREATE_DATE, 'YYYY/MM/DD hh24:mi:ss') <= '2024/03/21 08:42:53'
ORDER BY CREATE_DATE DESC

优先选择方式1，它在逻辑正确性和查询性能上都是最优解。

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