Length of Service calculation in OBIEE with years, months and days

Martin van Donselaar

@Jerry Casey Thanks for your contribution, however, Please do not copy content from other sources here, but link to the content instead.

Jerry Casey

Thanks.  Just edited my answer.

aPsikus

Thank you Jerry.

One additional question: to have it in one field it need to be concatenated. But CONCAT worsk only with CHAR. To have CHAR, it need to be changed to INT (as othwerwise CAST to CHAR do not work, becuase there is a lot of place after comma).

But when CAST Month calc to INT, it is changed/increased by 1. Any idea why?

Jerry Casey

I believe the issue is that the result of the MOD statement is not an integer, even though displayed as one. So when you cast to an integer, it is applied to 6.77..., not 6 in your first example.  So, that "Months" statement has to be nested in a TRUNC function (bolded below).  This statement:

EVALUATE('TO_CHAR(%1,%2)' as char,EVALUATE('TRUNC(%1)' as integer, (EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,date '2018-05-09',date '1952-10-16') /12)),'99')||' years, '||EVALUATE('TO_CHAR(%1,%2)' as char,EVALUATE('TRUNC(%1)',EVALUATE('MOD(%1,%2)' as integer,(EVALUATE('MONTHS_BETWEEN(%1,%2)' as integer,date '2018-05-09',date '1952-10-16')),12)),'99')||' months, '||EVALUATE('TO_CHAR(%1,%2)' as char,31*(EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,date '2018-05-09',date '1952-10-16')-EVALUATE('TRUNC(%1)' as double,EVALUATE('MONTHS_BETWEEN(%1,%2)' as integer,date '2018-05-09',date '1952-10-16'))),'99')||' days'

Produces this:

Also, I'm using "Evaluate(TO_CHAR)" because when I tried to use CAST(xxx as char) on the server functions,  I couldn't concatenate them without getting an error message.

aPsikus

Great hint.

Thank you for help.