How to find out age from Birthday

Age Calculation On Oracle

Topic Introduction: In this tutorial, I want to see how can we get age from birthdays. Age Calculation On Oracle can use any kind of duration between two dates. It will be returned on Year, Month and Day. Here I will give SQL Query and Function to find out the age. 

SELECT year||' year '||month||' month '||day||' days' AGE

FROM(

select trunc(months_between(:P_EDATE,:P_SDATE)/12) year,

trunc(mod(months_between(:P_EDATE,:P_SDATE),12)) month,

trunc(:P_EDATE-add_months(:P_SDATE,trunc(months_between(:P_EDATE,:P_SDATE)/12)*12+trunc(mod(months_between(:P_EDATE,:P_SDATE),12)))) day

from (Select to_date(TO_CHAR(:P_SDATE,'DDMMYYYY'),'DDMMYYYY') from dual)

);

In this query, we have used to parameters P_SDATE & P_EDATE  and the values are 01-FRB-91 & 29-SEP-22. This returned result is below. 

We can also create PL/SQL functions to get results. function code is given below.

CREATE OR REPLACE FUNCTION DURATION_CALCULATION(P_SDATE  IN DATE,P_EDATE IN DATE)

RETURN VARCHAR2

IS

   V_AGE   VARCHAR2(500);

BEGIN

SELECT year||' year '||month||' month '||day||' days'

INTO V_AGE

FROM(

select trunc(months_between(P_EDATE,P_SDATE)/12) year,

trunc(mod(months_between(P_EDATE,P_SDATE),12)) month,

trunc(P_EDATE-add_months(P_SDATE,trunc(months_between(P_EDATE,P_SDATE)/12)*12+trunc(mod(months_between(P_EDATE,P_SDATE),12)))) day

from (Select to_date(TO_CHAR(P_SDATE,'DDMMYYYY'),'DDMMYYYY') from dual)

);

   RETURN V_AGE;

END;

/

SQL Query using Function

select DURATION_CALCULATION(:P_SDATE,:P_EDATE) AGE from dual;