Oracle Age calculation from Date of birth and Today
SQL> select trunc(months_between(sysdate,dob)/12) year,
2 trunc(mod(months_between(sysdate,dob),12)) month,
3 trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day
4 from (Select to_date('15122000','DDMMYYYY') dob from dual);
YEAR MONTH DAY
---------- ---------- ----------
9 5 26
SQL>
SELECT
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;
This is easy and straight to the point.
For business logic I usually find a decimal number (in years) is useful:
select months_between(TRUNC(sysdate),
to_date('15-Dec-2000','DD-MON-YYYY')
)/12
as age from dual;
AGE
----------
9.48924731
Age (full years) of the Person:
SELECT
TRUNC(months_between(sysdate, per.DATE_OF_BIRTH) / 12) AS "Age"
FROM PD_PERSONS per