Oracle Transactional Business Intelligence

Products Banner

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

43
Views
3
Comments

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.

Comments

  • The above issue is in OTBI Analysis. Please help.

  • Prudence K
    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?

  • 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