Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
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?

share|improve this question
up vote 3 down vote accepted

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
share|improve this answer
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 '12 at 4:55
I think you are mixing up LEFT and RIGHT. Did you try both using a simple example? – Aaron Bertrand Nov 15 '12 at 5:23
oops yes aaron i just thought the reverse thanks for helping me – Biju jose Nov 15 '12 at 5:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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