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
.
.
.
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
.
.
.
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.
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
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
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
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
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.