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, 12 * in_years);
RETURN new_date;
END;
The example function expects to receive a date and a number of months to add or subtract as arguments. If one of those arguments is left out, then PL/SQL will raise an ORA-06553 error, and the example also raises a special NO_DATA_FOUND error if one or both of the arguments are NULL. The return value will be the input date but in the newly calculated year.
How It Works
Oracle provides a couple of different ways to calculate dates based upon the addition or subtraction of years. One such technique is to use the ADD_MONTHS function that was discussed in Recipe 7-3, as the solution to this recipe demonstrates. Simple mathematics allow you to multiply the number of years passed into the ADD_MONTHS function by 12 since there are 12 months in the year. Essentially this technique exploits the ADD_MONTHS function to return a date a specified number of dates into the future.
Note See Recipe 7-3 for discussion of a corner case involving the use of ADD_MONTHS on a date that represents the final day of that date's month.
You can use this same technique to subtract a number of years from the specified date by passing a negative integer value that represents the number of years you want to subtract. For instance, if you wanted to subtract five years from the date 06/01/2000, then pass a -5 to the function that was created in the solution to this recipe. The following query demonstrates this strategy.
Here's the result:
06/01/2005
Using the ADD_MONTHS function works well for adding or subtracting a rounded number of years. However, if you wanted to add one year and six months, then it would take another line of code to add the number of months to the calculated date. The function in the next example is a modified version of the CALCULATE_DATE_YEARS function that allows you to specify a number of months to add or subtract as well:
CREATE OR REPLACE FUNCTION calculate_date_years (in_date DATE,
in_years IN NUMBER,
in_months IN NUMBER DEFAULT 0)
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, 12 * in_years);
-- Additional code to add the number of months to the calculated date
IF in_months != 0 THEN
new_date := ADD_MONTHS(new_date, in_months);
END IF;
RETURN new_date;
END;
Using the new function, you can pass positive integer values for the number of years and the number of months to add years or months to the date, or you can pass negative values for each to subtract years or months from the date. You can also use a combination of positive and negative integers for each to obtain the desired date. Since the modified function contains a DEFAULT value of 0 for the number of months, it is possible to not specify a number of months, and you will achieve the same result as the function in the solution to the recipe.
As you can see, this function is a bit easier to follow, but it does not allow for one to enter a negative value to subtract from the date. All the techniques described within this section have their own merit. However, it is always a good rule of thumb to write software so that it is easy to maintain in the future. Using this rule of thumb, the most favored technique of the three would be to use the ADD_MONTHS function as demonstrated in the solution. Not only is this function easy to understand but also widely used by others within the Oracle community.