The ADD_MONTHS Function

The ADD_MONTHS function returns a new date with the specified number of months added to the input date. The specification for ADD_MONTHS is as follows:

FUNCTION ADD_MONTHS (date_in IN DATE, month_shift NUMBER) RETURN DATEFUNCTION ADD_MONTHS (month_shift NUMBER, date_in IN DATE) RETURN DATE

ADD_MONTHS is an overloaded function. You can specify the date first and then the number of months by which you want to shift that date, or you can list the month_shift parameter first and then the date. Either order is acceptable, and both arguments are required.

If the month_shift parameter is positive, ADD_MONTHS returns a date for that number of months into the future. If the number is negative, ADD_MONTHS returns a date for that number of months in the past. Here are some examples that use ADD_MONTHS:

· Move ahead by 3 months:

ADD_MONTHS ('12-JAN-1995', 3) --> 12-APR-1995

· Move back by 12 months:

ADD_MONTHS (-12, '12-MAR-1990') --> 12-MAR-1989

ADD_MONTHS always shifts the date by whole months. You can provide a fractional value for the month_shift parameter, but ADD_MONTHS will round down to the next whole number nearest zero, as shown in the following example. Pay special attention to the direction in which a negative value of months is rounded.

BEGIN --Adding 1.9999 is the same as adding 1 DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', 1.9999)); DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', 1)); DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', -1.9999)); DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', -1));END;

The output is:

31-MAR-198931-MAR-198931-JAN-198931-JAN-1989

If you want to shift a date by a fraction of a month, simply add or subtract the required number of days. PL/SQL supports direct arithmetic operations between date values. For more information, see Section 10.3 earlier in this chapter.

If the input date to ADD_MONTHS does not fall on the last day of the month, the date returned by ADD_MONTHS falls on the same day in the new month as in the original month. If the day number of the input date is greater than the last day of the month returned by ADD_MONTHS, the function sets the day number to the last day in the new month. The following example illustrates both of these rules. The first call to ADD_MONTHS advances 15-Jan one month to 15-Feb. Because there is no 31st day in February, the second call returns the last day in February:

BEGIN DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('15-JAN-1995', 1)); DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('31-JAN-1995', 1));END;

The output is:

15-FEB-199528-FEB-1995

This behavior is perfectly reasonable. However, what if the input date falls on the last day of the month, and the new month has more days than the original month? If you shift two months forward from 28-FEB-1994, do you get back 30-APR-1994 (the last day of April) or 28-APR-1994 (the same day in the new month as in the old month)? The answer is that if the input date represents the last day of the month, so will the output date:

BEGIN DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1994', 2));END; 30-APR-1994

If you pass to ADD_MONTHS a day representing the last day of the month, PL/ SQL always returns the last day of the resulting month, regardless of the number of actual days in each of the months. This behavior makes sense in some situations, but not in others. Be aware of it! (See the upcoming sidebar for a function that can help you get around this quirk.)

Getting Around ADD_MONTH's Month-End Quirk The fact that ADD_MONTHS always returns a month-end date whenever the input date represents a month-end date is sometimes inconvenient. The following PL/SQL function, my_add_months, will preserve the original day of the month whenever possible: FUNCTION my_add_months ( date_in IN DATE, months_shift IN NUMBER)RETURN DATE IS date_out DATE; day_in NUMBER; day_out NUMBER;BEGIN date_out := ADD_MONTHS(date_in, months_shift); day_in := TO_NUMBER(TO_CHAR(date_in,'DD')); day_out := TO_NUMBER(TO_CHAR(date_out,'DD')); IF day_out > day_in THEN date_out := date_out - (day_out - day_in); END IF; RETURN date_out;END; The only time this function adjusts the day of the month is when the day happens to be beyond the end of the target month. Add 1 month to 31-Jan-2002, and because February has only 28 days you'll get 28-Feb-2002 as the result. However, add 1 to 28-Feb-2002, and you'll get 28-Mar-2002—the day is preserved whenever possible. This function also preserves any time-of-day components such as hours, minutes, and seconds.