Reg: Timestampdiff Issue

Question

Hi Team,

I’m trying to find date diff between 2 date fields in RPD using below expression data type is date.

Timestampdiff (SQl_TSI_Day, OPEN_DATE, ACCEPTED_DATE)

But when I check the query in view log it was calculating in reverse like below was there any reason for this

SAWITH0 AS (select sum ((TRUNC (ACCEPTED_DATE) - TRUNC (OPEN_DATE))) AS c1

I tried the same expression in report level as well but it was calculating in reverse can someone suggest.

Version we are using OBIEE 12C

Thanks,

Gianni Ceresa · Answer

It isn't calculating the reverse difference, it is just doing what the function is supposed to do.

By using it as you did, you will get the number of days between OPEN_DATE and ACCEPTED_DATE, and that supposed that OPEN_DATE is an older date than ACCEPTED_DATE.

Thanks to the fact that an Oracle database can use mathematical operations on dates as they act as numbers, to get the number of days you just do a subtraction between the newer date and the older date.

Work as expected, make perfectly sense.

What does shock you? The result is correct, right?