Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-05
4
Medium Priority
?
4,053 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
.
.
.
0
Comment
Question by:robjay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Expert Comment

by:DrJekyll
ID: 10759718
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
 

Author Comment

by:robjay
ID: 10759892
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
 
LVL 8

Accepted Solution

by:
Danielzt earned 600 total points
ID: 10761979
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
 

Author Comment

by:robjay
ID: 10767393
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question