In this article, I’ll explain how the very useful ADD_MONTHS function works and use some examples.

## Purpose of the Oracle ADD_MONTHS Function

The purpose of the Oracle ADD_MONTHS function is to **add a specified number of months to a date and return another month**.

## Syntax

The syntax of the ADD_MONTHS function is:

`ADD_MONTHS ( input_date, number_months )`

The function returns a DATE value.

## Parameters

The parameters of the ADD_MONTHS function are:

**input_date**(mandatory): This is the starting date, or the date that you will add a number of months to.**number_months**(mandatory): This is an integer value that represents the number of months to add to*input_date*.

Some things to note about this function:

- The
*input_date*can be any DATETIME value, or a value that can be converted to DATE (such as a VARCHAR2 formatted as a date). - The
*number_months*can be any integer value, or a value that can be converted to an integer (such as a VARCHAR2 formatted as a whole number). - The return type is always DATE, even if the data type supplied for input_date is not a DATE.

## What If The Number Of Days Are Different?

This is a valid question. What would happen if you try to add to a month using ADD_MONTHS, with the original date being at the end of the month, and the resulting month doesn’t have that day?

Well, the function would **return the last day of the resulting month**.

For example, adding 3 months to January 31 will return April 30 (because April only has 30 days).

## Is There An Oracle ADD_DAYS Function Like ADD_MONTHS?

**No**, there is no ADD_DAYS function in Oracle.

However, you can **easily add or subtract a day from a date by just adding the number to it**.

For example, to add 3 days to today’s date:

`SELECT SYSDATE + 1 FROM dual;`

To subtract 7 days from today’s date:

`SELECT SYSDATE - 7 FROM dual;`

There doesn’t need to be an extra function to do this.

** **

## Is There An Oracle ADD_YEARS Function Like ADD_MONTHS?

**No, there is also no ADD_YEARS function in Oracle.**

The good news is you can use ADD_MONTHS with a number_months parameter of 12, to add a single year. You can use multiples of 12 to add more years.

Also, you can use negative numbers to subtract years (such as -12).

See the Examples section below for more details.

## Can Oracle ADD_MONTHS Subtract Months?

**Yes, you can** subtract months using the ADD_MONTHS function by simply using a negative number as the number_months parameter.

See the Examples section below for more details.

## How Do You Get The First Day Of The Month Using Oracle ADD_MONTHS?

There are **several ways** to get the first day of the month, and one is using ADD_MONTHS.

`SELECT TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1) FROM dual;`

You can also use the TRUNC function by itself:

`SELECT TRUNC(SYSDATE,'MM') FROM dual;`

## Examples of the Oracle ADD_MONTHS Function

Here are some examples of the ADD_MONTHS function.

### Example 1

This example shows how to add one month to the current date.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 1) as ADD_MONTH_RESULT
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 06/SEP/2022 |

The ADD_MONTH_RESULT shows that one month has been added to the date.

### Example 2

This example shows how to add six months to the current date.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 6) as ADD_MONTH_RESULT
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 06/FEB/2023 |

The result is six months added to the current date, which results in Feb 6, 2023.

### Example 3

This example shows how to add a larger number of months.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 41) as ADD_MONTH_RESULT
FROM dual;
```

Result

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 06/JAN/2026 |

The result is in January 2026, which is 41 months into the future.

### Example 4

This example shows how to subtract months by using a negative number

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, -6) as ADD_MONTH_RESULT
FROM dual;
```

Result

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 06/FEB/2022 |

The result is in February 2022, which is 6 months before the SYSDATE.

### Example 5

This example shows the use of a number inside quotes, which is actually a CHAR but should get treated like a numeric value.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, '5') as ADD_MONTH_RESULT
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 06/JAN/2023 |

The result is a date in January 2023, which is five months into the future.

### Example 6

This example shows a text value inside the number_months parameter.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 'Six') as ADD_MONTH_RESULT
FROM dual;
```

Result:

ORA-01722: invalid number 01722. 00000 - "invalid number"

This is because I used the value of “Six”, which isn’t actually a number.

### Example 7

This example uses a specific date as the input date, instead of SYSDATE.

```
SELECT TO_DATE('08-JUL-2015') as INPUT_DATE,
ADD_MONTHS(TO_DATE('08-JUL-2015'), 1) as ADD_MONTH_RESULT
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT |

08/JUL/2022 | 08/AUG/2022 |

As you can see, a single month is added to the specified date.

### Example 8

This example uses a decimal number as the number of months to add.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 2.3) as ADD_MONTH_RESULT_LOW,
ADD_MONTHS(SYSDATE, 2.9) as ADD_MONTH_RESULT_HIGH
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT_LOW |
ADD_MONTH_RESULT_HIGH |

06/AUG/2022 | 06/OCT/2022 | 06/OCT/2022 |

In both cases, the value is the same. This is because the decimal value has been rounded down to 2, even if the 2.9 would normally be rounded up to 3.

### Example 9

This example shows how you can add a year to a date.

```
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 12) as ADD_MONTH_RESULT
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 06/AUG/2023 |

The result is a date one year into the future.

### Example 10

This example shows how to find the first day of the month using the ADD_MONTHS function.

```
SELECT SYSDATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)) as FIRST_DAY
FROM dual;
```

Result:

SYSDATE |
ADD_MONTH_RESULT |

06/AUG/2022 | 01/AUG/2023 |

As you can see, the result is the first day of the month that was provided.

## Similar Functions

Some functions which are similar to the ADD_MONTHS function are:

- TRUNC – Truncates a datetime into a date format.
- LAST_DAY – Returns the date of the last day of the specified month.

You can find a full list of Oracle SQL functions here.