ORA-01858 means the format model expects digits at some position, for a day, month, or year, and the string has a letter or other character there. Typical causes are a letter O typed instead of a zero, or a date written with a month name read by a numeric format. Oracle AI Database 26ai words it as A non-numeric character was found instead of a numeric character.
Reproduce the Error
Example:
select to_date('2026-O3-15', 'YYYY-MM-DD') from dual;
select to_date('March 15, 2026', 'DD-MM-YYYY') from dual;Output:
Error starting at line : 1
In command -
select to_date('2026-O3-15', 'YYYY-MM-DD') from dual
Error at Command Line : 1 Column : 16
Error report -
SQL Error: ORA-01858: A non-numeric character was found instead of a numeric character.
Error starting at line : 2
In command -
select to_date('March 15, 2026', 'DD-MM-YYYY') from dual
Error at Command Line : 2 Column : 16
Error report -
SQL Error: ORA-01858: A non-numeric character was found instead of a numeric character.- 2026-O3-15 has the letter O where MM expects digits.
- March 15, 2026 starts with letters, but the format starts with DD.
Fix: A Format That Matches, or a Safe Default
Example:
select to_date('March 15, 2026', 'Month DD, YYYY', 'nls_date_language = English') as parsed from dual;
select to_date('2026-O3-15' default null on conversion error, 'YYYY-MM-DD') as bad_value from dual;Output:
PARSED ______________ 15-MAR-2026 BAD_VALUE ____________
Month DD, YYYY reads the written-out date. For data that may contain typos, DEFAULT NULL ON CONVERSION ERROR returns NULL for bad values, so they can be found and corrected instead of stopping the query.
Things to Know
- VALIDATE_CONVERSION(value AS DATE, 'format') returns 0 for strings that cannot be converted, which helps find bad rows.
- Store dates in DATE or TIMESTAMP columns, not text, to avoid conversion errors altogether.
Related Guides
Conclusion
ORA-01858 means a character other than a digit sits where the format expects a number. Fix the typo, use a format that matches written-out dates, or convert with a default and correct the bad rows.
