Lazai - 2 years ago 164
SQL Question

# How to get age in years,months and days using Oracle

I'm trying to print for each person its age using this format :

E.g : 19 years , 8 months , 13 days.

I've googled a lot and I've noticed that there is a specific function to calculate the difference between dates

DATEDIFF
.

However this function does not exist in
SQL*Plus
, so I went on trying using
MONTHS_BETWEEN()
and some operators.

My attempt:

SELECT name , ' ' ||
FLOOR(MONTHS_BETWEEN(to_date(SYSDATE),to_date(date_of_birth))/12)||' years ' ||
FLOOR(MOD(MONTHS_BETWEEN(to_date(SYSDATE),to_date(date_of_birth)),12)) || ' months ' ||
FLOOR(MOD(MOD(MONTHS_BETWEEN(to_date(SYSDATE),to_date(date_of_birth)),12),4))|| ' days ' AS "Age"
FROM persons;

My issue relies on getting the days. I don't know how should I calculate the days , using this function ('tried dividing by 4 , or 30); I'm thinking my logic is bad but I can't figure it out , any ideas ?

Very similar to Lalit's answer, but you can get an accurate number of days without assuming 30 days per month, by using add_months to adjust by the total whole-month difference:

select sysdate,
hiredate,
trunc(months_between(sysdate,hiredate) / 12) as years,
trunc(months_between(sysdate,hiredate) -
(trunc(months_between(sysdate,hiredate) / 12) * 12)) as months,
trunc(sysdate)
from emp;

SYSDATE    HIREDATE        YEARS     MONTHS       DAYS
---------- ---------- ---------- ---------- ----------
2015-10-26 1980-12-17         34         10          9
2015-10-26 1981-02-20         34          8          6
2015-10-26 1981-02-22         34          8          4
2015-10-26 1981-04-02         34          6         24
2015-10-26 1981-09-28         34          0         28
2015-10-26 1981-05-01         34          5         25
2015-10-26 1981-06-09         34          4         17
2015-10-26 1982-12-09         32         10         17
2015-10-26 1981-11-17         33         11          9
2015-10-26 1981-09-08         34          1         18
2015-10-26 1983-01-12         32          9         14
2015-10-26 1981-12-03         33         10         23
2015-10-26 1981-12-03         33         10         23
2015-10-26 1982-01-23         33          9          3

You can verify by reversing the calculation:

with tmp as (
select trunc(sysdate) as today,
hiredate,
trunc(months_between(sysdate,hiredate) / 12) as years,
trunc(months_between(sysdate,hiredate) -
(trunc(months_between(sysdate,hiredate) / 12) * 12)) as months,
trunc(sysdate)