Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 71
dbms_crypto.decrypt   errors out 6 34
ORA-02288: invalid OPEN mode 2 59
Using SQL*PLUS issue with where statement with trunc function 3 29
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

828 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