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

Question

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?

Riyaz Ali-Oracle · Answer

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;

Aarthi Natarajan-Oracle · Answer

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