Oracle Age calculation from Date of birth and Today

Question

I want to calculate the current Age from Date of Birth in my Oracle function.

What I am using is (Today-Dob)/30/12, but this is not accurate as some months have 31 days.

I need to get the correct age with the maximum precision. How can I do that?

Answer 1

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>

Answer 2

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

Answer 3

SELECT 
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;

This is easy and straight to the point.