Solved

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

Posted on 2004-04-05
4
3,935 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 150 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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