select STUFF(
STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)),  6)
            , 3, 0, ':')
        , 6, 0, ':') 
    AS [LastRunDuration (HH:MM:SS)]
 from sysjobhistory

The query I have given above works fine but I am trying to understand it more clear. My observations are:

  1. First we cast the run duration into varchar and with a length 6 and append six 0's to right of it which becomes if its is 10 then 00000010
  2. After we use the stuff function to insert : in between this here it is at the 3rd position so the above one becomes 00:000010
  3. Again we use stuff to insert the : symbol to the sixth position so it becomes 00:00:0010

But when I run the query if there is a values 10 it shows as 00:00:10.

How that can be?

1 Answer 1


In your first bullet you say the value becomes 00000010. That is incorrect. It actually becomes 000010 because of the RIGHT( , 6).

To clarify, compare the difference:

SELECT LEFT('00000010', 6), RIGHT('00000010', 6);


------  ------
000000  000010
  • but when using RIGHT() in the above exp it's right(00000010,6) which gives you the output 000000,so how it will become 000010
    – Biju jose
    Nov 15, 2012 at 4:55
  • 1
    I think you are mixing up LEFT and RIGHT. Did you try both using a simple example? Nov 15, 2012 at 5:23
  • oops yes aaron i just thought the reverse thanks for helping me
    – Biju jose
    Nov 15, 2012 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.