Oracle PL/SQL/PL SQL/Date Calculation

Performing Calculations on a Converted Date

 
SQL>
SQL> DECLARE
  2       v_Convert_Date DATE;
  3  BEGIN
  4       v_Convert_Date := TO_DATE("042199","MMDDYY") + 5;
  5       DBMS_OUTPUT.PUT_LINE("The converted date is: " || v_Convert_Date);
  6  END;
  7  /
The converted date is: 26-APR-2099 00:00:00
PL/SQL procedure successfully completed.
SQL>
SQL> --

Your own add month function

 
SQL>
SQL> CREATE OR REPLACE FUNCTION new_add_months (
  2     date_in IN DATE,
  3     months_shift IN NUMBER
  4     )
  5     RETURN DATE
  6  IS
  7     retval                        DATE;
  8  BEGIN
  9     retval := ADD_MONTHS (date_in, months_shift);
 10
 11     IF date_in = LAST_DAY (date_in)
 12     THEN
 13        retval := LEAST (retval,TO_DATE (TO_CHAR (date_in, "DD") || TO_CHAR (retval, "MMYYYY"),"DDMMYYYY"));
 14     END IF;
 15
 16     RETURN retval;
 17  EXCEPTION
 18     WHEN OTHERS
 19     THEN
 20        RETURN retval;
 21  END new_add_months;
 22  /
Function created.
SQL>
SQL> select new_add_months(sysdate, 3) from dual;
NEW_ADD_M
---------
19-SEP-08