TO_CHAR不同格式差异：SQL日期范围查询结果不一致问题

核心差异 #

两个SQL的唯一区别是to_char函数转换日期输出的字符串格式不同，最终导致BETWEEN运算的逻辑完全不同。

根因解释 #

BETWEEN对字符串类型的值执行比较时，是按字符逐位做字典序排序比较，而非按照日期的实际时间先后判断：

• 错误写法使用DD-MM-YYYY格式转换日期
  转换后的字符串格式是日-月-年，字典序和日期的实际顺序完全不匹配。例如31-03-2019（2019年3月31日，实际在你要的统计区间之前）的字符串比较结果为：'31-03-2019' >= '01-04-2019' AND '31-03-2019' <= '31-03-2020'，会被误判为符合区间条件，所以最终统计结果不为0。
• 正确写法使用YYYYMMDD格式转换日期
  转换后的字符串格式是年+月+日，字典序和日期的实际时间先后顺序完全一致，比较时会严格按照你需要的日期区间筛选，所以返回了正确的0。

最佳实践建议 #

尽量不要将日期转成字符串做区间判断，直接用日期类型比较更稳妥，还能利用日期字段的索引提升查询性能，参考写法：

SELECT COUNT(*)
FROM transaction_tb 
WHERE STATUS NOT IN ('Wrong', 'Dont') 
AND date BETWEEN DATE'2019-04-01' AND DATE'2020-03-31';

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