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);
