SQL & PL/SQL

Hi all

Database Version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
Database Version: PL/SQL Release 10.2.0.5.0 - Production

I just realised that the interval function doesn't take leap years into account.

SQL> select to_date('29-02-2008','dd-mm-yyyy') + interval '1' year 
  2    from dual;
select to_date('29-02-2008','dd-mm-yyyy') + interval '1' year
                                      *
ERROR at line 1:
ORA-01839: date not valid for month specified

I've searched through the documentation as best I can and could find no mention of this.

If I want to add one year to a date, do I have to manually calculate the number of days or do something like incrementing only the year portion and then manually verifying that it doesn't fall on the 29th of february if it's not a leap year?

edit:

just to clarify, day and minute appear to work for instance but month and year don't:

SQL> select to_date('28-02-2009','dd-mm-yyyy') + interval '1' day
  2    from dual;

TO_DATE('
---------
01/MAR/09

1 row selected.

Elapsed: 00:00:00.00
SQL> 
SQL> select to_date('28-02-2009 23:59','dd-mm-yyyy hh24:mi') + interval '1' minute 
  2    from dual;

TO_DATE('
---------
01/MAR/09

1 row selected.

Elapsed: 00:00:00.00
SQL> 
SQL> select to_date('29-01-2009','dd-mm-yyyy') + interval '1' month
  2    from dual;
select to_date('29-01-2009','dd-mm-yyyy') + interval '1' month
                                      *
ERROR at line 1:
ORA-01839: date not valid for month specified


Elapsed: 00:00:00.00
SQL> 
SQL> select to_date('29-02-2008','dd-mm-yyyy') + interval '1' year
  2    from dual; 
select to_date('29-02-2008','dd-mm-yyyy') + interval '1' year
                                      *
ERROR at line 1:
ORA-01839: date not valid for month specified

Edited by: WhiteHat on Jul 14, 2011 10:31 AM