How to Calculate AGE between two dates in OBIEE

Age in OBIEE

First thing is you need to have two date fields to calculate age. In OBIEE we have TIMEDIFF time function to calculate difference between two dates.

Example is shown below

Timediff(SQL_TSI_YEAR, Date of birth, CURRENT_DATE)

DATE OF BIRTH column is derived from the database

CURRENT_DATE is the predefined variable to fetch sysdate.

Output of this query will give you the age.

There is a big flaw in this query because it always finds the age by differentiating year.

Below query will even consider day and month of the birth date and calculates exact age between dates.

 SELECT 

--Year

TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) YEAR,

--Month

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

--Day

TRUNC(SYSDATE-ADD_MONTHS(DOB,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12)*12 +TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,DOB),12)))) DAY

,

--Difference between Birth Month and Current Month

TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM')) MON,

--Difference between Birth Day and Current Day

TO_NUMBER(TO_CHAR(SYSDATE,'DD'))-TO_NUMBER(TO_CHAR(DOB,'DD')) days,

--Compare the above two factors and decide which year. This query will calculate even day difference between the dates.

CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM'))<=0 OR 

TO_NUMBER(TO_CHAR(SYSDATE,'DD'))-TO_NUMBER(TO_CHAR(DOB,'DD'))<=0 THEN

CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM'))=0 then 

TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) -1

ELSE 

TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) 

END

ELSE 

CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM'))>0

THEN TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) END

end

correct_age

from (Select to_date('16042015','DDMMYYYY') dob from dual);