Oracle SQL, Function for finding out Age from DOB

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google