I've been chasing this issue as well, and stumbled across the same code that the original poster found. Unfortunately, that code, which I traced back as early as 2002, does not appear to work... at least not in SQL 2005.

Given a run_date of 20090112, a run_time of 190239 and a run_duration of 2210, you'd expect the code in the original post to produce 2009-01-12 19:24:49.000. Instead it produces 2009-01-12 19:39:29.000, which is exactly what you'd get when you add 2,210 seconds to a time instead of adding 22 minutes and 10 seconds. I consulted BOL and sure enough, run_duration is an integer representation of time in HHMMSS format, and not an integer containing the number of seconds it took for a job to run.

Here's the code I originally found (link):

endTime = DATEADD

(

SECOND,

jh.run_duration,

CAST

(

CONVERT(VARCHAR, jh.run_date)

+ ' ' + STUFF(STUFF(RIGHT('000000'

+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')

AS DATETIME

)

)

By my eye it's a little easier to read than the code from the original post, although you'll see that the link includes that code as well. This code has the same flaw the the code from the OP does as well.

Here's my modification. For formatting style isn't what I use, but I wanted to keep this as close as possible to the original post.

endTime = CAST

(

CONVERT(VARCHAR, jh.run_date)

+ ' ' + STUFF(STUFF(RIGHT('000000'

+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')

AS DATETIME) +

STUFF(STUFF(RIGHT('000000'

+ CONVERT(VARCHAR,jh.run_duration),6),5,0,':'),3,0,':')

Maybe I've got this whole thing wrong and I've simply overlooked something, but it honestly appears to me that the same example code we've all been using is just flat-out wrong.

Edit: Replaced "history." with "jh." in my example to align with rest of code.