ADD_MONTHS

The ADD_MONTHS function in Oracle is used to add or subtract months from a date. This function is useful when you need to perform date arithmetic, such as calculating future or past dates based on a certain number of months.

 

Syntax:

ADD_MONTHS(date, number_of_months)

• date: The starting date from which the months are to be added or subtracted. This can be a DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE value.
• number_of_months: The number of months to add (positive number) or subtract (negative number). This can be a positive or negative integer.

 

Return Type:

• The return type is the same as the input date type.
• If the input is a DATE, the result is a DATE.
• If the input is a TIMESTAMP, the result is a TIMESTAMP.

 

Key Features of ADD_MONTHS:

1.     Adding and Subtracting Months:

• Positive values for the number_of_months parameter will add months to the date.
• Negative values will subtract months from the date.

Example:

• Adding 3 months to 2025-02-28:

SELECT ADD_MONTHS(DATE '2025-02-28', 3) FROM dual;

-- Result: 2025-05-28

• Subtracting 6 months from 2025-02-28:

SELECT ADD_MONTHS(DATE '2025-02-28', -6) FROM dual;

-- Result: 2024-08-28

2.     Handling Invalid Dates:

• If the date passed is the last day of the month and the resulting month doesn't have the same day (e.g., adding months to 2025-01-31), Oracle will automatically adjust the result to the last day of the resulting month.

Example:

• Adding 1 month to 2025-01-31:

SELECT ADD_MONTHS(DATE '2025-01-31', 1) FROM dual;

-- Result: 2025-02-28

Since February 2025 has only 28 days, the result will be adjusted to 2025-02-28.

3.     Leap Year Adjustments:

• The function correctly handles leap years. For example, if you add 12 months to a date in a leap year, it will return the same day of the month in the following year, adjusting for leap years where necessary.

Example:

• Adding 12 months to 2024-02-29 (Leap Year):

SELECT ADD_MONTHS(DATE '2024-02-29', 12) FROM dual;

-- Result: 2025-02-28

Since 2025 is not a leap year, it adjusts to 2025-02-28.

4.     Adjusting to the Last Day of the Month:

• When adding months to a date where the original day exceeds the number of days in the target month, Oracle adjusts the result to the last day of the month.

Example:

• Adding 1 month to 2025-01-31 (January 31st):

SELECT ADD_MONTHS(DATE '2025-01-31', 1) FROM dual;

-- Result: 2025-02-28 (February has 28 days)

This is the default behavior when dealing with dates that might not exist in the target month.

5.     Adjusting TIMESTAMP Types:

• You can use ADD_MONTHS with TIMESTAMP values as well. The function will maintain the time part of the TIMESTAMP when adding months.

Example:

• Adding 3 months to a TIMESTAMP value:

SELECT ADD_MONTHS(TIMESTAMP '2025-01-15 10:30:00', 3) FROM dual;

-- Result: 2025-04-15 10:30:00

 

Examples of ADD_MONTHS Usage:

1. Adding 5 months to a specific date:

SELECT ADD_MONTHS(DATE '2024-08-15', 5) FROM dual;

-- Result: 2025-01-15

In this example, we added 5 months to 2024-08-15, and the result is 2025-01-15.

2. Subtracting 3 months from a specific date:

SELECT ADD_MONTHS(DATE '2024-08-15', -3) FROM dual;

-- Result: 2024-05-15

Here, we subtracted 3 months from 2024-08-15, and the result is 2024-05-15.

3. Handling edge cases with ADD_MONTHS:

• Adding months to a date that is the last day of the month.

SELECT ADD_MONTHS(DATE '2024-01-31', 1) FROM dual;

-- Result: 2024-02-29 (Leap year, February has 29 days)

• Adding months to a non-leap year date:

SELECT ADD_MONTHS(DATE '2025-02-28', 1) FROM dual;

-- Result: 2025-03-28 (February 2025 has 28 days)

 

Important Points to Note:

1.     Day of the Month Adjustments:

• If the start date is the last day of the month, ADD_MONTHS will adjust the result to the last day of the month in the resulting month if the new month does not have that day.

Example:

SELECT ADD_MONTHS(DATE '2025-01-31', 1) FROM dual;

-- Result: 2025-02-28 (February does not have 31st)

2.     Handling Negative Values:

• ADD_MONTHS can be used with negative numbers to subtract months from a date, which is helpful when calculating past dates.

3.     Impact of Leap Years:

• The function adjusts dates according to the leap year rule. For example, adding 12 months to 2024-02-29 (Leap Year) will result in 2025-02-28 because 2025 is not a leap year.

4.     Non-leap Year February Dates:

• When adding months to dates like 2024-02-29, Oracle adjusts the result depending on the target month (whether it’s a leap year or not).

 

Performance Considerations:

• ADD_MONTHS is a basic function in Oracle, and it performs efficiently for most use cases, especially when used in SELECT statements.
• However, when using this function with large datasets, it's important to consider the overall execution plan and the impact of complex date calculations.

 

Common Use Cases:

1.     Calculating Due Dates or Expiration Dates:

• Add months to a given date to calculate a due date, such as subscription periods, payment terms, etc.

Example:

SELECT ADD_MONTHS(SYSDATE, 6) FROM dual;

-- Result: Adds 6 months to the current date (e.g., due date for a subscription).

2.     Calculating Previous or Future Dates:

• Subtract months to calculate past due dates or future milestones.

Example:

SELECT ADD_MONTHS(SYSDATE, -3) FROM dual;

-- Result: Subtracts 3 months from the current date.

3.     Date Calculations for Billing Cycles:

• Used to determine when the next billing cycle will occur by adding months to the current date.

 

Summary:

The ADD_MONTHS function in Oracle is a powerful tool for date arithmetic, allowing you to add or subtract months from a given date. It handles edge cases like leap years, month lengths, and the last day of the month automatically, making it suitable for a wide range of business applications. Whether calculating future due dates, subscription terms, or adjusting timestamps, ADD_MONTHS simplifies date manipulation in Oracle.