ADD_MONTHS Function in Oracle

 In Oracle, ADD_MONTHS is a date function used to add or subtract a number of months to a given date.

---

✅ Syntax

ADD_MONTHS(date, number_of_months)

• date: A valid DATE or TIMESTAMP value.
• number_of_months: Can be positive (add months) or negative (subtract months).

---

📌 Examples

🔹 Add 3 months to today's date

SELECT ADD_MONTHS(SYSDATE, 3) AS future_date FROM dual;

🔹 Subtract 6 months from a specific date

SELECT ADD_MONTHS(TO_DATE('15-MAR-2025','DD-MON-YYYY'), -6) AS past_date FROM dual;

🔹 Used in table query

SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 12) AS one_year_anniversary
FROM employees;

---

⚠️ Edge Case Handling

• If the original date is the last day of a month, and the resulting month has fewer days, Oracle returns the last day of the resulting month.

Example:

SELECT ADD_MONTHS(TO_DATE('31-JAN-2025','DD-MON-YYYY'), 1) FROM dual;
-- Result: 28-FEB-2025 (or 29-FEB in a leap year)

---