Oracle SQL, Function for finding out Age from DOB

advertisements

_____________________________________________________________________________________________________________________

PLSQL Script for Function

create or replace function dob (birth char) return char as

x date;

age varchar2(30);

begin

  select to_date(birth,'ddmmyyyy') into x from dual;

  select trunc(months_between(sysdate, x)/12) ||' Years '||

trunc(mod(months_between(sysdate, x),12)) ||' Months '||

trunc(sysdate-add_months(x, trunc(months_between(sysdate, x)/12)*12+trunc(mod(months_between(sysdate,x),12))))|| ' Days ' into age

from dual;

return age;

exception

when others then

return 'Invalid date: Pls enter date in ddmmyyyy format';

end;

Output

SQL> select dob(30041986) from dual; 

DOB(30041986)

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

27 Years 11 Months 24 Days 

SQL> select dob(12323232323) from dual;

DOB(12323232323)

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

Invalid date: Pls enter date in ddmmyyyy format

SQL

select trunc(months_between(sysdate, dob)/12) year,

trunc(mod(months_between(sysdate, dob),12)) month,

trunc(sysdate-add_months(dob, trunc(months_between(sysdate, dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day

from (select to_date('28051987','ddmmyyyy') dob from dual); 

Output

      YEAR      MONTH        DAY

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

        26         10         27

_____________________________________________________________________________________________________________________

Website Stats