Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-4. Adding Years to a Date

Problem

You are developing an application that requires date calculations to be performed. You need to determine how to add to a specified date. You may also want to subtract years.

Solution

Create a function that will calculate a new date based upon the number of years that you have specified. If you want to subtract a number of years from a date, then pass a negative value for the number of years. The following code implements this functionality:

CREATE OR REPLACE FUNCTION calculate_date_years (in_date DATE,                                     in_years NUMBER) RETURN DATE AS   new_date    DATE; BEGIN   IF in_date is NULL OR in_years is NULL THEN     RAISE NO_DATA_FOUND;   END IF;   new_date := ADD_MONTHS(in_date, ...