Use INTERVAL types : INTERVAL « PL SQL Data Types « Oracle PL/SQL Tutorial






SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_college_deadline TIMESTAMP;
  3  BEGIN
  4     v_college_deadline := TO_TIMESTAMP('06/06/2004', 'DD/MM/YYYY')
  5                           + INTERVAL '12-3' YEAR TO MONTH;
  6
  7     DBMS_OUTPUT.PUT_LINE('My daughter leaves for college in '
  8                          ||v_college_deadline);
  9  END;
 10  /
My daughter leaves for college in 06-SEP-16 12.00.00.000000 AM

PL/SQL procedure successfully completed.

SQL>
SQL>








21.8.INTERVAL
21.8.1.INTERVAL includes two datatypes that allow you to set appropriate durations more precisely
21.8.2.Interval value expressions
21.8.3.DAY TO SECOND examples
21.8.4.workarounds for defining intervals
21.8.5.Add some years and months
21.8.6.Add some days, hours, minutes, and seconds
21.8.7.Add 1 year, 2 months, 3 days, 4 hours, 5 minutes, 6.7 seconds
21.8.8.Adding and subtracting intervals
21.8.9.Interval multiplication
21.8.10.length of service for INTERVAL YEAR TO MONTH
21.8.11.Show some interval division
21.8.12.Storing elapsed time with INTERVAL DAY TO SECOND
21.8.13.Use INTERVAL types
21.8.14.Add date interval to timestamp
21.8.15.Double the interval