calculate Age!!!

[sathiya]

Hai all!!

I have one problem for my developing pace. To calculate the age.

that is i give the my date of birth like 10-mar-1972
find my age from sysdate.

i.e sysdate - '10-mar-1972'

thank you

[pando]

well how do you want it?

In years, months and days?

[Raminder]

select round(months_between(sysdate, to_date('10-Mar-1972', 'dd-Mon-yyyy'))/12)
from dual
/

[Raminder]

Below is a function I created for calculating age in years and months.

create or replace function age(dob in date)
return varchar2
as
yrs number(3);
mnths number(2);
str varchar2(20);
Begin
yrs:=floor(months_between(sysdate, dob)/12);
mnths:=mod(months_between(sysdate, dob),12);
str:=yrs||' Years '||mnths||' Months';
return str;
end; 
/