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

    You are trying to convert a string to a date using the TO_DATE function, but the string does not match the format mask.


    An alpha numeric character was found when a numeric character was expected.


    eg.:

    SQL> select to_date('AA/10/2005', 'DD/MM/YYYY') from dual;
    select to_date('AA/10/2005', 'DD/MM/YYYY') from dual
                   *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected


    Fix the input string or change the format mask in order to resolve the error.

    SQL> select to_date('16/10/2005', 'DD/MM/YYYY') from dual;

    TO_DATE('
    ---------
    16-OCT-05




    It can happen sometimes that you are expecting this error, but you still get a correct date.

    eg.:

    SQL> select to_date('17OCT2005','DDMMYYYY') from dual;

    TO_DATE('
    ---------
    17-OCT-05

    This is standard Oracle behaviour (string to date conversion rules).

    Oracle tries to convert the string (OCT) to a valid month before applying the format mask.



    If you do not want this behaviour, use the FX formatting.


    eg.:

    SQL> select to_date('17OCT2005','fxDDMMYYYY') from dual;
    select to_date('17OCT2005','fxDDMMYYYY') from dual
                   *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected


    SQL> select to_date('17102005','fxDDMMYYYY') from dual;

    TO_DATE('
    ---------
    17-OCT-05

Adverteren bij Daisycon
Forum Messages
No messages
Add your message for ORA-01858
Name:email:
Validation Code:odzepcp7y52ci3ydm
Enter Code above:
Title:
State your problem: