Skip to Main Content

Cloud Connectivity

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Balakumar231286Feb 7 2025 — edited Feb 7 2025

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

Comments

InoL Apr 29 2024

How doc gen treats nulls values?

You have to be more specific. Are your referring to the pre-built Document Generator function?

If so:

When my query return null in some columns

What is the JSON that is generated?

You should always include the element in the JSON, with an empty value. Don't leave it out.

Francois Robert-Oracle Jan 8 2025 — edited on Jan 8 2025

Since September 2024, when a tag has no corresponding value in the JSON data or the value is null, the tag is replaced by an empty string.

See the September Release Notes

1 - 2

Post Details

Added on Feb 7 2025
1 comment
54 views