Your own add month function : Date Calculation « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Date Calculation

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

 

Related examples in the same category