Question

How to calculate age in years months and days in oracle sql?

Answer 1

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)