OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to add years, months and days to a date

June 28th, 2007 By Eddie Awad

Let’s say you want to add 5 years, 2 months and 10 days to the date 8/14/2002, here are a few ways to do that:

Using NUMTOYMINTERVAL:

SQL> SELECT   TO_DATE ( '8/14/2002', 'mm/dd/yyyy' )
  2         + NUMTOYMINTERVAL ( 5, 'year' )
  3         + NUMTOYMINTERVAL ( 2, 'month' )
  4         + 10
  5    FROM DUAL
  6  /

TO_DATE('
---------
24-OCT-07

Using INTERVAL:

SQL> SELECT TO_DATE ( '8/14/2002', 'mm/dd/yyyy' )
  2         + INTERVAL '05-02' YEAR TO MONTH
  3         + 10
  4    FROM DUAL
  5  /

TO_DATE('
---------
24-OCT-07

Using ADD_MONTHS:

SQL> SELECT ADD_MONTHS (
  2             TO_DATE ( '8/14/2002', 'mm/dd/yyyy' ),
  3             62 )
  4         + 10
  5    FROM DUAL
  6  /

ADD_MONTH
---------
24-OCT-07

2 Responses to “How to add years, months and days to a date”

  1. Laurent Schneider Says:

    make sure you know what you want to do with variable months

    what does adding one month to 30 june means?

    what does adding 18 years to 29 february means?

    adding y2m interval often leads to

    SQL> select date '2007-07-31' - interval '00-01' year to month from dual
    select date '2007-07-31' - interval '00-01' year to month from dual
    *
    Error at line 0
    ORA-01839: date not valid for month specified
    
  2. Eddie Awad Says:

    Thanks for the tip Laurent.

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question