We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

robjay
robjay asked
on
Medium Priority
4,795 Views
Last Modified: 2010-05-18
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
.
.
.
Comment
Watch Question

Commented:
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.

Author

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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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


Unlock the solution to this question.
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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.