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

Mik A.M. · Mar 2, 2016 4:18PM

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

Gianni Ceresa · Mar 2, 2016 4:44PM

Hi,

A simple CASE WHEN your_value < 10 THEN '0' END || your_value

You will need to manage the CAST to avoid errors (so number for the WHEN check and char/varchar for the concatenation.

Daniel Willis · Mar 2, 2016 7:19PM

Whenever I need to add leading 0s to something I always just prefix 0 to everything and then take the substring/right of the string

e.g. here is an example in Excel. I don't know what the best method is for performance but I like the simplicity of this.

Ebin · Mar 3, 2016 2:24PM

Hi Mik,

You can use custom strings in OBIEE to convert seconds into hh:mm:ss format. Use the Custom Numeric Format as [duration(sec)] hh:mm:ss

https://docs.oracle.com/cd/E28280_01/bi.1111/e10544/format.htm#BIEUG11528

Mik A.M. · Mar 4, 2016 12:27PM

Ebin,

Thank you so much for this! I was overthinking my calculations so much. By using your solution it makes it so much easier. Cannot thank you enough.

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

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

Answers