Hello Everyone,
We have a requirement in OTBI analysis to calculate the number of days between 2 dates in the following format – XX YEARS XX MONTHS XX DAYS.
We have tried some approaches by using the DATETIME functions in OTBI. However, the output is not correct for all the date ranges.
Most of the date ranges it gives the correct output but some cases the accuracy is getting missed by 1 or 2 days.
Example, the below approach gives the correct days for the dates 08/01/20924 and 02/07/2025 (SYSDATE) as 0 YEARS 6 MONTHS 7 DAYS,
However, the same code gives incorrect days for the dates 05/01/20924 and 02/07/2025 (SYSDATE) as 0 YEARS 9 MONTHS 8 DAYS.
Instead of 7 DAYS it gives 8 DAYS.
"Years": FLOOR( "Days" / 365 ),
"Months": MOD(FLOOR(("Days" / 30.4375)), 12)
"Days": MOD("Days", 30.4375)
Please help us in sharing any catalog or any information, if anyone has implemented the same in any of the custom reports?
Thank you,
Bala