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
July 17th, 2007 at 1:22 am
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
July 17th, 2007 at 7:29 pm
Thanks for the tip Laurent.