Oracle PL/SQL/PL SQL/Date Calculation

Performing Calculations on a Converted Date

   <source lang="sql">

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> --

</source>
   
  

Your own add month function

   <source lang="sql">

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

</source>