Solved

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

Posted on 2004-04-05

Oracle Database

1 solution

3,990 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

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

Join The Community

4 Comments

LVL 5

Expert Comment

by:DrJekyll

ID: 107597182004-04-05

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 Comment

by:robjay

ID: 107598922004-04-05

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

LVL 8

Accepted Solution

by:Danielzt

Danielzt earned 150 total points

ID: 107619792004-04-05

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.

Author Comment

by:robjay

ID: 107673932004-04-06

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

Featured Post

Question has a verified solution.

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

Zero Downtime technique to rebuild Oracle tables

Article by: Greg

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…

Oracle Database

Some Useful Settings in JasperReports Server

Article by: Doug

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.