Query using TO_DATE TO_CHAR failing with ORA-01858 or ORA-01843
(Doc ID 790098.1)
Last updated on NOVEMBER 25, 2019
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 02-Jul-2012***
An error is being generated on some clients when executing a query using TO_DATE(TO_CHAR .
The errors indicate the data is invalid for what is expected to be returned. If you add/change a query hint the error may change slightly.
You may see:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01843: not a valid month
An example of the scenario:
The DATA in SCOTT.EMP is as follows:
A session, instance, or database NLS parameter has changed or you may have upgraded clients.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!