Get Age in Years and Months | Convert to a Human-Readable Format in Oracle

Calculating Age in Years and Months in Oracle

To calculate age in years and months from a given date (e.g., date of birth), you can use the MONTHS_BETWEEN function.

---

✅ 1️⃣ Get Age in Years and Months

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

🔹 Explanation:

• MONTHS_BETWEEN(SYSDATE, DOB) / 12 → Gives total age in years (including fraction).
• TRUNC(... / 12) → Extracts the whole number of years.
• MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, DOB)), 12) → Gets the remaining months.

🟢 Example Output for DOB = '1990-05-15' (assuming today is '2025-02-13')

YEARS   MONTHS
----------------
34      8

🔹 Age: 34 years and 8 months

---

✅ 2️⃣ Convert to a Human-Readable Format

If you want the output as a single formatted string:

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

Output:

AGE
------------------
34 Years 8 Months

---