OTBI: Diff of Two dates to get Age is rounded to next highest value

Srinivas Challa

Summary

OTBI: Diff of Two dates to get Age is rounded to next highest value

Content

OTBI: Diff of Two dates to get Age is rounded to next highest value

Example: If difference of Age is 48.8 Its displayed as 49 instead of 48 even after using FLOOR or TRUNCATE.

Below is the formula I used still unable to succeed.

FLOOR(TimeStampDiff(SQL_TSI_YEAR, "Dependent Data"."Dependent Date Of Birth", Current_Date))

Your help is highly appreciated.

Srinivas Challa

The above issue is in OTBI Analysis. Please help.

Prudence K

Srinivas - please try this formula:

FLOOR((TIMESTAMPDIFF(SQL_TSI_DAY, "Person"."Person Date Of Birth", CURRENT_DATE))/365.25)

I could not get anything else to work, but this one seems to do it.  It's interesting that Oracle's recommendation is just TIMESTAMPDIFF(SQL_TSI_DAY, "Person"."Person Date Of Birth", CURRENT_DATE) as this will calculate with the rounding, yet I know that when we report birthdays, we report what has passed and thus someone would be 48 not 49 if they are 48.8 years old

I'd be curious if others have formulas that work too?

Matthias Ludewig

Here is another formula that should work quite fine. As days are compared with days, months with months and years with years this is more precise than the FLOOR method which will show a year less in specific cases based on the leap year adjustments:

 

YEAR(CURRENT_DATE)-YEAR("Worker"."Employee Date Of Birth")
 -

CASE
  WHEN
        MONTH("Worker"."Employee Date Of Birth") > MONTH(CURRENT_DATE)
    THEN 1

    ELSE
    CASE
      WHEN
            (MONTH("Worker"."Employee Date Of Birth") = MONTH(CURRENT_DATE)
        AND DAY("Worker"."Employee Date Of Birth") > DAY(CURRENT_DATE))
      THEN 1
  ELSE 0
  END
  END