oracle sql query giving error

Question

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:

with NLS_DATE_FORMAT='DD-MON-RR' - this query works fine.
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.

Rohit · Accepted Answer · 2013-02-18 18:26:27Z

3

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;

answered Feb 18, 2013 at 18:26

Rohit

5836 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2013-02-18 18:32:40Z

2

You have an implicit char-to-date conversion, in the add_months() call; the argument you're passing is a string, not a date. The to_char() you have inside that is redundant, and causing the error when your NLS_DATE_FORMAT doesn't match the format you're using in that to_char():

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

I'm not entirely sure what you're doing though... if you want the first day of the month that date is in, you can do this:

SELECT to_char(trunc(to_date('01-02-2013', 'dd-mm-yyyy'), 'MM'),
    'dd-mm-yyyy') FROM dual;

This uses the TRUNC(date) function to effectively round down to the start of the month.

edited Feb 18, 2013 at 18:32

answered Feb 18, 2013 at 18:27

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Collectives™ on Stack Overflow

oracle sql query giving error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related