Calculate number of DAYS between two dates in XX YEARS XX MONTHS XX DAYS format in OTBI

Balakumar231286

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/2024 and 02/07/2025 as 0 YEARS 6 MONTHS 7 DAYS,

However, the same code gives incorrect days for the dates 05/01/2024 and 02/07/2025 as 0 YEARS 9 MONTHS 8 DAYS.

Instead of 7 DAYS it gives 8 DAYS.

Can you please let me know , why same Query gives different days as result?

To calculate YEARS , MONTHS & DAYS, I used the below formula

"Years": FLOOR( "Number of Days" / 365.25 ),
"Months": MOD(FLOOR(("Number of Days" / 30.4375)), 12)
"Days": MOD("Number of 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?

Aarthi Natarajan-Oracle

Hi Balakumar,

Did you try to set the column date format first so that its uniform and then use it for calculation?

Regards

Aarthi Natarajan

Riyaz Ali-Oracle

Calculate the Difference Using Built-In Date Functions

Example in SQL:

sql
SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('02-07-2025','MM-DD-YYYY'), TO_DATE('05-01-2024','MM-DD-YYYY')) / 12) AS Years,
MOD(TRUNC(MONTHS_BETWEEN(TO_DATE('02-07-2025','MM-DD-YYYY'), TO_DATE('05-01-2024','MM-DD-YYYY'))), 12) AS Months,
TO_DATE('02-07-2025','MM-DD-YYYY') - ADD_MONTHS(TO_DATE('05-01-2024','MM-DD-YYYY'),
TRUNC(MONTHS_BETWEEN(TO_DATE('02-07-2025','MM-DD-YYYY'), TO_DATE('05-01-2024','MM-DD-YYYY')))) AS Days
FROM DUAL;