Simon's SQL

Since, msdb.dbo.sysjobhistory has run_duration as INT data type.
Need to convert to find out actual job start time and end time.

create function [dbo].[fn_get_job_start_datetime] 
(@run_date int, @run_time int)
returns datetime
as
begin
	return convert(datetime,rtrim(@run_date)) 
			+ (@run_time*9 
				+ @run_time%10000*6 
				+ @run_time%100*10) / 216e4
end
go

create function [dbo].[fn_get_job_end_datetime] 
(@run_date int, @run_time int, @run_duration int)
returns datetime
as
begin
	if @run_duration<0  -- some of replication job has minus(-) value.
		set @run_duration = 99999999
	return convert(datetime,rtrim(@run_date)) 
			+ (@run_time*9 
				+ @run_time%10000*6 
				+ 25*@run_duration) / 216e4
end
go

select top 10 j.name as job_name
	 , h.run_date
	 , h.run_time
	 , h.run_duration
	 , h.run_status
	 , dbo.fn_get_job_start_datetime(h.run_date, h.run_time) 
		as job_start_datetime
	 , dbo.fn_get_job_end_datetime(h.run_date, h.run_time, h.run_duration) 
		as job_end_datetime
  from msdb.dbo.sysjobs j
  join msdb.dbo.sysjobhistory h on j.job_id = h.job_id