7-3. Adding a Number of Months to a Date
Problem
You want to add some number of months to a date. For example, you are developing a payment application for a company, and it requires payments every six months. You need to enable the application to calculate the date six months in the future of the current date.
Note This recipe's solution also works for subtracting months. Simply “add” a negative number of months.
Solution
Use the ADD_MONTHS function to add six months onto the given date. Doing so will enable your application to create bills for future payments. This technique is demonstrated in the following example:
DECLARE
new_date DATE;
BEGIN
new_date := ADD_MONTHS(sysdate,6);
DBMS_OUTPUT.PUT_LINE('The newly calculated date is: ' || new_date);
END;
This simple technique will enable you to add a number of months to any given date. As with any other logic, this could easily be encapsulated into a function for the specific purpose of producing a billing date that was six months into the future of the current date. Such a function may look something like the next example:
CREATE OR REPLACE FUNCTION calc_billing_date IS
BEGIN
RETURN ADD_MONTHS(sysdate, 6);
END;
Although this function does not do much besides encapsulate logic, it is a good idea to code such functions when developing a larger application where this type of calculation may be performed several times. It will help to maintain consistency and alleviate maintenance issues if the date calculation ever needs to change. You could simply make the change within the function rather than visiting all the locations in the code that use the function.
How It Works
Oracle provides the ADD_MONTHS function to assist with date calculations. This function has two purposes—to add or subtract a specified number of months from the given date. The syntax for use of the ADD_MONTHS function is as follows:
ADD_MONTHS(date, integer)
You can also use the function to subtract months from the given date. If the function is passed a negative integer in place of the month's argument, then that number of months will be subtracted from the date. The following example demonstrates this functionality:
DECLARE
new_date DATE;
BEGIN
new_date := ADD_MONTHS(sysdate,-2);
DBMS_OUTPUT.PUT_LINE('The newly calculated date is: ' || new_date);
END;
As you can see from the example in Figure 7-3, the negative integer is the only change made to the code in order to achieve a subtraction of months rather than an addition. As a result, the example in this figure will return the current date minus two months.
In the case that you are attempting to add months to a date that represents the last day of the month, the ADD_MONTHS function works a bit differently than you might expect. For instance, if it is August 31 and you want to add one month, then you would expect the calculation to resolve to September 31, which is not possible. However, ADD_MONTHS is smart enough to return the last day of September in this case. The following code provides a demonstration:
DECLARE
new_date DATE;
BEGIN
new_date := ADD_MONTHS(to_date('08/31/2010','MM/DD/YYYY'),1);
DBMS_OUTPUT.PUT_LINE('The last day of next month is: ' || new_date);
END;
The following is the resulting output:
The last day of next month is: 30-SEP-10
PL/SQL procedure successfully completed. In general, if your source date is the late day of its month, then your result date will be forced to the last day of its respective month. Adding one month to September 30, for example, will yield October 31.