ADD_MONTHS Function

Adding months to a date is not as easy as adding weeks, because all months don’t have the same number of days some have 30, some 31, some 28, and at times even 29. To add one month to a date, we need to know how many days that calendar month will have. Therefore, adding months to a date by converting those months to a number of days involves lots of homework, which is error-prone. Fortunately, Oracle provides a built-in SQL function to add months to dates. This function is called ADD_MONTHS, and we call it as follows:

ADD_MONTHS (date, number)

The syntax elements are:

date

Specifies a database column defined as type DATE or a string with a date in the default date format.

Number

Specifies the number of months to add to the input date.

The following example shows the computation of an employee’s biannual review date by using ADD_MONTHS to add six months to the employee’s HIRE_DATE:

SELECT FNAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 6) REVIEW_DATE FROM EMPLOYEE;