Analytics Software

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!

Add Leading Zeros to hh:mm:ss when value is less than 10

Mik A.M.Mar 2 2016 — edited Mar 4 2016

Hello all,

I'm new to OBIEE 11g so Forgive me if this is a simple fix.

I am subtracting two timestamps and am outputing them as an hh:mm:ss format. While my formula works, I'd like to add a leading zero if any segment of the value is less than zero.

For instance: if the output is 12:4:5 I want the output to be 12:04:05.

Here is the formula I'm using:

Cast(TimeStampDiff(SQL_TSI_SECOND,start_time,End_Time)/(24*60) as VARCHAR(10)) ||':'|| Cast(Mod(TimeStampDiff(SQL_TSI_SECOND,start_time,End_Time)/(60), 60) as VARCHAR(10)) ||':'|| Cast(Mod(TimeStampDiff(SQL_TSI_SECOND,start_time,End_Time), 60) as VARCHAR(10))

Any help would be greatly appreciated.

Mik

This post has been answered by Ebin on Mar 3 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 1 2016

Added on Mar 2 2016

#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee

4 comments

1,597 views