ADD_MONTHS

The ADD_MONTHS function returns the date resulting from date plus integer months.

SQL syntax

ADD_MONTHS(Date,Integer)

Parameters

ADD_MONTHS has the parameters:

Parameter Description

Date

A datetime value or any value that can be converted to a datetime value.

Integer

An integer or any value that can be converted to an integer.

Description

  • The return type is always DATE regardless of the data type of date. Supported data types are DATE, TIMESTAMP, ORA_TIMESTAMP and ORA_DATE.

  • Data types TIME, TT_TIME, TT_DATE and TT_TIMESTAMP are not supported.

  • If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

Examples

Call the ADD_MONTHS function to add 1 month to date January 31, 2007. The last day of February is returned. 

Command> SELECT ADD_MONTHS (DATE '2007-01-31', 1) FROM dual;
< 2007-02-28 00:00:00 >
1 row found.

ADD_MONTHS returns data type DATE if date is of type TIMESTAMP: 

Command> DESCRIBE SELECT ADD_MONTHS (TIMESTAMP '2007-01-31 10:00:00', 1) 
         FROM dual;
Prepared Statement:
  Columns:
    EXP                             DATE NOT NULL

Use the HR schema to select the first 5 rows of the employees table, showing employee_id, last_name and hire_date. Create new table temp_hire_date using the CREATE TABLE ... AS SELECT statement. Call ADD_MONTHS to add 23 months to the original hire_date. 

Command> SELECT FIRST 5 employee_id, last_name, hire_date FROM employees;
< 100, King, 1987-06-17 00:00:00 >
< 101, Kochhar, 1989-09-21 00:00:00 >
< 102, De Haan, 1993-01-13 00:00:00 >
< 103, Hunold, 1990-01-03 00:00:00 >
< 104, Ernst, 1991-05-21 00:00:00 >
5 rows found.
Command> CREATE TABLE temp_hire_date (employee_id, last_name,
           hire_date) AS SELECT FIRST 5 employee_id, last_name,
        ADD_MONTHS (hire_date, 23) FROM employees;
5 rows inserted.
Command> SELECT * FROM temp_hire_date;
< 100, King, 1989-05-17 00:00:00 >
< 101, Kochhar, 1991-08-21 00:00:00 >
< 102, De Haan, 1994-12-13 00:00:00 >
< 103, Hunold, 1991-12-03 00:00:00 >
< 104, Ernst, 1993-04-21 00:00:00 >
5 rows found.