Oracle中如何比较不同格式日期并查询指定日期范围内的行数据

核心错误点 #

• 你对DATE类型的字段tg_konfi错误使用了to_date()函数：to_date()的作用是将字符串类型的内容转换为日期类型，传入DATE类型参数时会触发Oracle的隐式类型转换，转换规则依赖当前会话的NLS_DATE_FORMAT、NLS_DATE_LANGUAGE参数，参数不匹配时会生成错误的日期值，最终导致无匹配结果。
• 你指定的转换格式DD-MON-YY中MON为英文月份简写，若当前会话语言为非英文（如中文），无法识别FEB这类英文月份标识，也是转换失败的常见原因。

正确写法 #

直接使用DATE类型字段做范围匹配即可，不需要额外转换：

SELECT * FROM 你的表名 
WHERE tg_konfi BETWEEN TO_DATE('2021-01-01','YYYY-MM-DD') AND TO_DATE('2021-10-07','YYYY-MM-DD');

额外优化建议 #

如果tg_konfi字段存储的内容包含时间部分，TO_DATE('2021-10-07','YYYY-MM-DD')默认对应10月7日0点，会漏掉10月7日0点之后的所有记录。要覆盖10月7日全天数据，推荐用以下写法（避免对字段加函数导致索引失效）：

SELECT * FROM 你的表名 
WHERE tg_konfi >= TO_DATE('2021-01-01','YYYY-MM-DD')
AND tg_konfi < TO_DATE('2021-10-08','YYYY-MM-DD');

原写法失效原理补充 #

你原代码中to_date(tg_konfi,'DD-MON-YY')的实际执行逻辑等价于：
to_date(to_char(tg_konfi, 会话默认NLS_DATE_FORMAT), 'DD-MON-YY')
只要会话默认日期格式和DD-MON-YY不一致，或者语言不匹配，转换结果就会和实际日期偏差，自然查询不到符合条件的记录。

内容的提问来源于stack exchange，提问作者Fajar Satya Sanjaya