Date time calculation function : Utility Function « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Function

SQL>
SQL> CREATE OR REPLACE FUNCTION expiration_date (
  2      good_for_period_in IN INTERVAL YEAR TO MONTH
  3  )
  4      RETURN TIMESTAMP
  5  IS
  6     bad_month EXCEPTION;
  7     PRAGMA EXCEPTION_INIT (bad_month, -1839);
  8
  9     todays_date TIMESTAMP;
 10     result_date TIMESTAMP;
 11  BEGIN
 12     todays_date := TRUNC(SYSTIMESTAMP);
 13     LOOP
 14        BEGIN
 15           result_date := todays_date + good_for_period_in;
 16        EXCEPTION
 17           WHEN bad_month THEN
 18              todays_date := todays_date - INTERVAL '1' DAY;
 19           WHEN OTHERS THEN
 20              RAISE;
 21        END;
 22
 23        EXIT WHEN result_date IS NOT NULL;
 24     END LOOP;
 25     RETURN result_date;
 26  END;
 27  /

Function created.