1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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