SQL how to calculate age from birth date

Question

is anyone know how to calculate AGE from birth of date?? using to_char?

  birth_date  
  ------------------  
  2/4/1972
  23/4/1999
  21/8/2001

Answer 1

I think the best way is months_between():

trunc(months_between(sysdate, dob) / 12)

Answer 2

If you want to calculate year, month and day then here goes your solution. (You can also choose to select year only. Then just ignore month and day column)

 create table temptable (birth_date date);

 insert into temptable values(date '1972-04-02');
 insert into temptable values(date '1999-04-23');
 insert into temptable values(date '2001-08-21');

Query:

 select trunc(months_between(sysdate,birth_date)/12) year,
            trunc(mod(months_between(sysdate,birth_date),12)) month,
            trunc(sysdate-add_months(birth_date,trunc(months_between(sysdate,birth_date)/12)*12+trunc(mod(months_between(sysdate,birth_date),12)))) day
     from temptable

Output:

YEAR	MONTH	DAY
49	2	1
22	1	11
19	9	13

db<fiddle here