ORA-01839: date not valid for month specified (to_yminterval function)

I don't understand why to_yminterval('01-02') doesn't work and to_yminterval('03-03') does with my data?

SQL> select ename, hiredate, hiredate + to_yminterval('01-02') from emp
ERROR:
ORA-01839: date not valid for month specified

no rows selected

SQL> c/01-02/03-03
  1* select ename, hiredate, hiredate + to_yminterval('03-03') from em
SQL> /

ENAME      HIREDATE         HIREDATE+TO_YMIN
---------- ---------------- ----------------
SMITH      12.17.1980 00:00 03.17.1984 00:00
ALLEN      02.20.1981 00:00 05.20.1984 00:00
WARD       02.22.1981 00:00 05.22.1984 00:00
JONES      04.02.1981 00:00 07.02.1984 00:00
MARTIN     09.28.1981 00:00 12.28.1984 00:00
BLAKE      05.01.1981 00:00 08.01.1984 00:00
.
.
.
robjayAsked:
Who is Participating?
 
DanielztCommented:
try this, you will understand.

select to_date('30-Jan-1987','dd-mon-yyyy')+to_yminterval('01-01') from dual;

19:41:12  Processing ...
19:41:12  select to_date('30-Jan-1987','dd-mon-yyyy')+to_yminterval('01-01') from dual
19:41:12                                              *
19:41:12  ORA-01839: date not valid for month specified

select to_date('30-Jan-1987','dd-mon-yyyy')+to_yminterval('01-02') from dual;
30-Mar-1988

as you see, to_date('30-Jan-1987','dd-mon-yyyy')+to_yminterval('01-01'), you will get '30-Feb-1988', but this a wrong date, because it will no have more than 29 days in February.

so, Oracle does not handle this well when you date in February and the date has more than 29 days.




0
 
DrJekyllCommented:
There is nothing wrong with your sql.  What version of 9 are you using?  I would suggest patching to the latest version. It appears to be a bug.
0
 
robjayAuthor Commented:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
0
 
robjayAuthor Commented:
You are correct, by excluding the two hiredates that will fall in Feb with the to_yminterval function applied, I do not get the error.  Below is the successful query excluding the enames with the problem hiredates and then a query showing  the actual hiredates:

SQL> select ename, hiredate, hiredate + to_yminterval('01-02')
  2  from emp
  3  where ename not in ('JONES', 'SOYUHU');

ENAME      HIREDATE  HIREDATE+
---------- --------- ---------
SMITH      17-DEC-80 17-FEB-82
ALLEN      20-FEB-81 20-APR-82
WARD       22-FEB-81 22-APR-82
MARTIN     28-SEP-81 28-NOV-82
.
.
.SQL> select ename, hiredate
  2  from emp
  3  where ename in ('JONES', 'SOYUHU');

ENAME      HIREDATE
---------- ---------
JONES      30-DEC-99
SOYUHU     30-DEC-99


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here