TO_DATE函数返回值解析及Oracle日期比较报错问题排查

问题背景 #

原以为Contracts表的TradeDate是DATE类型，执行以下查询：

SELECT DISTINCT 
    TradeDate,
    TO_DATE('2023/07/24', 'YYYY/MM/DD')
FROM Contracts
ORDER BY TradeDate DESC;

能正常返回结果，但TradeDate和TO_DATE返回值显示格式不同；添加WHERE子句做日期比较时：

SELECT DISTINCT 
    TradeDate,
    TO_DATE('2023/07/24', 'YYYY/MM/DD')
FROM Contracts
WHERE TradeDate = TO_DATE('2023/07/24', 'YYYY/MM/DD')
ORDER BY TradeDate DESC;

触发ORA-01861错误，后续发现TradeDate实际是字符串类型。需明确三个问题：

1. TO_DATE函数具体返回什么？
2. 为何比较时报错？
3. 日期显示格式不同的原因是什么？

问题解答 #

1. TO_DATE函数的返回值 #

TO_DATE()是Oracle的日期转换函数，接收字符串类型的日期值和对应格式掩码，返回Oracle原生DATE类型的数据。DATE类型在Oracle内部以数字形式存储（包含世纪、年、月、日、时、分、秒信息），本身没有固定显示格式——最终显示格式由会话的NLS_DATE_FORMAT参数控制。

2. 比较时报错的原因 #

执行TradeDate = TO_DATE('2023/07/24', 'YYYY/MM/DD')时，Oracle会触发隐式类型转换：因为TradeDate是字符串，Oracle会尝试把DATE类型的TO_DATE()结果转换成字符串，再和TradeDate做比较。

转换时会使用当前会话的NLS_DATE_FORMAT参数作为默认格式，如果这个格式和TradeDate字符串的实际格式不匹配，就会抛出ORA-01861: 文字与格式字符串不匹配错误。

举个例子：如果会话的NLS_DATE_FORMAT是DD-MON-RR，Oracle会把TO_DATE('2023/07/24', 'YYYY/MM/DD')转换成24-JUL-23这类字符串，再去和TradeDate的2023/07/24格式字符串对比，格式不匹配就会报错。

3. 显示格式不同的原因 #

• TradeDate是字符串类型，查询时直接输出它的原始字符串格式，不会做任何转换；
• TO_DATE()返回的DATE类型数据，会根据当前会话的NLS_DATE_FORMAT参数格式化成字符串后显示。

比如你的会话NLS_DATE_FORMAT是DD-MON-RR，那TO_DATE('2023/07/24', 'YYYY/MM/DD')就会显示成24-JUL-23，而TradeDate如果是2023/07/24格式的字符串，两者显示自然不一样。

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