MONTHS_BETWEEN Function in Oracle | Get Age in Years

MONTHS_BETWEEN Function in Oracle

The MONTHS_BETWEEN function in Oracle calculates the number of months between two dates.

---

✅ Syntax

MONTHS_BETWEEN(date1, date2)

• date1 – The later date.
• date2 – The earlier date.
• Returns a decimal number (fractional months included).
• If date1 is later than date2, the result is positive.
• If date1 is earlier than date2, the result is negative.
• If both dates have the same day, it returns an integer.
• If dates have different days, it calculates using fractional months.

---

✅ Examples

🔹 1️⃣ Basic Usage

SELECT MONTHS_BETWEEN(DATE '2024-02-01', DATE '2024-01-01') AS months_diff FROM DUAL;

Output:

MONTHS_DIFF
-----------
1

---

🔹 2️⃣ Handling Fractional Months

sql
SELECT MONTHS_BETWEEN(DATE '2024-02-15', DATE '2024-01-01') AS months_diff FROM DUAL;

Output:

MONTHS_DIFF
-----------
1.45161290322581

🔹 Since Jan has 31 days, the fraction (15/31 = ~0.48) is added.

---

🔹 3️⃣ When Order is Reversed

SELECT MONTHS_BETWEEN(DATE '2024-01-01', DATE '2024-02-01') AS months_diff FROM DUAL;

Output:

MONTHS_DIFF
-----------
-1

🔹 Negative value because date1 is before date2.

---

🔹 4️⃣ Comparing Same Date in Different Years

SELECT MONTHS_BETWEEN(DATE '2024-01-01', DATE '2023-01-01') AS months_diff FROM DUAL;

Output:

MONTHS_DIFF
-----------
12

🔹 1-year difference = 12 months.

---

🔹 5️⃣ Using SYSDATE

SELECT MONTHS_BETWEEN(SYSDATE, DATE '2023-06-15') AS months_diff FROM DUAL;

🔹 This will return the number of months between today and 2023-06-15.

---

✅ Practical Use Cases

🔹 1️⃣ Get Age in Years (Approximate)

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, DATE '1990-05-15') / 12) AS age FROM DUAL;

🔹 2️⃣ Find Employees with More Than 6 Months of Experience

SELECT emp_name, hire_date
FROM employees
WHERE MONTHS_BETWEEN(SYSDATE, hire_date) > 6;

---

✅ Important Notes

• Uses the day of the month for calculations.
• If dates fall at the end of the month, it normalizes calculations.
• If the day doesn’t exist in a month (e.g., Feb 30), Oracle adjusts it to the last day of the month.