oracle年龄截取到岁,怎么将出生日期转化成年龄,精确到天

select  sysdate,

nmc_age('2006-3-16 18:16:00'),

nmc_age('2006-3-16 17:16:00'),

nmc_age('2006-2-15 17:12:35'),

nmc_age('20050215171235'),

nmc_age(19790813),

nmc_age('2005-03-15'),

nmc_age('2005/03/15')

from dual;

SYSDATE        2006-3-16 17:23:18

NMC_AGE('2006-3-1618:16:00')        bad date !large than sysdate

NMC_AGE('2006-3-1617:16:00')        0 Years 0 Months 0 Days 00:07:18

NMC_AGE('2006-2-1517:12:35')        0 Years 1 Months 1 Days 00:10:43

NMC_AGE('20050215171235')        1 Years 1 Months 1 Days 00:10:43

NMC_AGE(19790813)        26 Years 7 Months 3 Days 17:23:18

NMC_AGE('2005-03-15')        1 Years 0 Months 1 Days 17:23:18

NMC_AGE('2005/03/15')        1 Years 0 Months 1 Days 17:23:18

------------------------------------------------------------

create or replace function nmc_age(p_in varchar2)return varchar2

as

type t_strtab is table of varchar2(50);

l_strtab t_strtab:=t_strtab('yyyymmddhh24miss',

'yyyy-mm-dd hh24:mi:ss',

'dd-mon-yyyy',

'dd-month-yyyy');

today    date:=sysdate;

birth_day date;

l_mons    number;

l_year    int:=0;

l_month   int:=0;

l_day     int:=0;

l_hms    varchar2(10);

l_return varchar2(40);

begin

for i in 1..l_strtab.count

loop

begin

birth_day:=to_date(p_in,l_strtab(i));

exception

when others then

null;

end;

exit when birth_day is not null;

end loop;

if birth_day is not null  then

l_mons:=months_between(today,birth_day);

if l_mons>=0 then

if l_mons>0 then

l_year  := floor(l_mons/ 12);

l_month := floor(mod(l_mons, 12));

l_day   := floor(mod(l_mons,1)*31);

l_hms   :=to_char(trunc(sysdate,'dd')+ mod(mod(l_mons,1)*31,1),'hh24:mi:ss');

l_return:=l_year||' Years '||l_month||' Months '||l_day||' Days '||l_hms;

elsif today-birth_day>=0 then

l_hms   :=to_char(trunc(sysdate,'dd')+mod(today-birth_day,1),'hh24:mi:ss');

l_return:=l_year||' Years '||l_month||' Months '||l_day||' Days '||l_hms;

else

l_return:='bad date !large than sysdate';

end if;

else

l_return:='bad date !large than sysdate';

end if;

else

l_return:='bad date format!';

end if;

return l_return;

end;