ORA-01858

ORA-01858: a non-numeric character was found where a numeric was expected

What causes this error?

An ORA-01858 error occurs when you convert a string or a number to date and you have passed some wrong values, then Oracle responds back with this message.

How to fix it

If your session is set to default date format of DD-MON-YY, execute the following and you will receive the error message:

SQL> select to_date('10-JAN-2001', 'DD-MM-YYYY') from dual;
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
no rows selected

When you are converting a string to a date, you have specified that the date is being passed in DD-MM-YYYY format. But you have passed the date in DD-MON-YYYY format. As the month is expected as a number by Oracle, but you have passed a character, Oracle is unable to translate the string to a number.

Do one of the following:

SQL> select to_date('10-JAN-2001', 'DD-MON-YYYY') from dual
2 /
TO_DATE('10
-----------
10-JAN-2001

OR

SQL> select to_date('10-01-2001', 'DD-MM-YYYY') from dual
2 /

TO_DATE('10
-----------
10-JAN-2001

When you are fetching strings from a table and converting them to dates, check the data in the table first before issuing the date format string. If the table contained strings, which are not date values and you tried to convert them to dates, then you would see this error being raised.

There is no predefined exception to handle this error. So you would have to create a user-defined error to handle it.