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

Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
39
Views
2
Comments
Balakumar231286
Balakumar231286 Rank 1 - Community Starter
edited Apr 11, 2025 10:48AM in Oracle Transactional Business Intelligence

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?

Answers

  • Aarthi Natarajan-Oracle
    Aarthi Natarajan-Oracle Rank 3 - Community Apprentice

    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
    Riyaz Ali-Oracle Rank 6 - Analytics Lead

    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;