4 Replies Latest reply on Mar 4, 2016 5:27 PM by Mik A.M.

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

    Mik A.M.

      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.