Job History View

SQL Agent stores job history as three packed integers - run_date (YYYYMMDD), run_time (HHMMSS) and run_duration (HHMMSS) - which makes any reporting a pain. This view decodes all three with the substring/char(7) trick, adds duration to start time to produce a real EndTime, and formats Duration as HH:MM:SS regardless of how many digits the raw value has. Drop it in msdb and every job history report gets proper timestamps.

CREATE VIEW JobHistory AS
select
	JobName,
	StepName,
	Convert(VarChar, StartTime, 109) as StartTime,
	Duration,
	Convert(VarChar, StartTime + cast('1/1/1900 ' + Duration as DateTime), 109) as EndTime
from
	(
		select
			j.name as JobName,
			jh.step_name as StepName,
			-- Add Run Duration Seconds
			dateadd(ss,cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
			-- Add Run Duration Minutes
			dateadd(mi, cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
			-- Add Run Duration Hours
			dateadd(hh, cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
			-- Add Start Time Seconds
			dateadd(ss, cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
			-- Add Start Time Minutes
			dateadd(mi, cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
			-- Add Start Time Hours
			dateadd(hh, cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
			convert(datetime, cast(run_date as char(8))))
			))))) as StartTime,
			CASE len(jh.run_duration)
				WHEN 1 THEN cast('00:00:0' + cast(jh.run_duration as char) as char (8))
				WHEN 2 THEN cast('00:00:' + cast(jh.run_duration as char) as char (8))
				WHEN 3 THEN cast('00:0' + Left(right(jh.run_duration,3),1) +':' + right(jh.run_duration,2) as char (8))
				WHEN 4 THEN cast('00:' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))
				WHEN 5 THEN cast('0' + Left(right(jh.run_duration,5),1) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))
				WHEN 6 THEN cast(Left(right(jh.run_duration,6),2) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8))
			END as Duration
		from
			sysjobs as j
		inner join sysjobhistory as jh
			on jh.job_id = j.job_id
	) as jh
GO