CODEWITHSUNDEEP
sqloracle-database
Andrew Mudamai
Andrew Mudamai

Reputation: 21

Oracle SQL conversion functions

Could someone please explain why this code 

to_date('25-JAN', 'DD-MON')

works and this doesn't

to_date(to_char(date_of_birth, 'DD-MON'), 'DD-MON')

running

to_char(date_of_birth, 'DD-MON')

which is the inner function on it's own works, but the later throws an 

"ORA-01839 error: date not valid for month specified".

Thanks

Upvotes: 2

Views: 576

Answers (2)

Álvaro González
Álvaro González

Reputation: 146630

Your code works fine with 25th January as date. The problem is that dates need to have a year. So when you do this:

TO_DATE('25-JAN', 'DD-MON')

... Oracle needs to build a full date and assumes current year. Proof:

SELECT TO_CHAR(TO_DATE('25-JAN', 'DD-MON'))
FROM DUAL

... prints:

25/01/2011 00:00:00

Of course, there's a month that does not always have the same number of days: February

SELECT TO_CHAR(TO_DATE('29-FEB-2000', 'DD-MON-YYYY'))
FROM DUAL
-- 29/02/2000 00:00:00

SELECT TO_CHAR(TO_DATE('29-FEB', 'DD-MON'))
FROM DUAL
-- ORA-01839: date not valid for month specified

Upvotes: 6

confucius
confucius

Reputation: 13327

You have an invalid day to a specific month in your table , for example :

SQL> select date '1996-01-29' + interval '1' month as col_1
  2  ,      date '1997-01-29' + interval '1' month as col_2
  3  ,      date '1997-08-11' - interval '3' month as col_3
  4  from   dual;
,      date '1997-01-29' + interval '1' month as col_2
                         *
ERROR at line 2:
ORA-01839: date not valid for month specified


    SQL>
    SQL> select date '1996-01-29' + interval '1' month as col_1
      2  ,      date '1997-01-28' + interval '1' month as col_2
      3  ,      date '1997-08-11' - interval '3' month as col_3
      4  from   dual;
    Enter...

    29-02-1996 28-02-1997 11-05-1997

    1 row selected.

look inside you table for invalid day of a month .

Upvotes: 0

Related Questions