Search code examples
sqloracle-databaseora-01858

oracle sql query giving error

Following query is giving error:

SELECT to_char(last_day(add_months(to_char(to_date('01-02-2013','dd-mm-yyyy'),
    'dd-MON-yyyy'),-1)) + 1,'dd-mm-yyyy') FROM dual;

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

I tried this on two systems:

  1. with NLS_DATE_FORMAT='DD-MON-RR' - this query works fine.

  2. With NLS_DATE_FORMAT='MM-DD-YYYY' - gives me error ORA-01858: a non-numeric character was found where a numeric was expected.

Any clues as to why this query is failing? I can't have the queries be dependent on the DATE format.

Solution

  • Why are you doing a to_char when calling add_months , you need to pass a date like 

    SELECT to_char(last_day(add_months(to_date('01-02-2013','dd-mm-yyyy'),
    ,-1)) + 1,'dd-mm-yyyy') FROM dual;