Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Find out sql job start time and end time

Posted by Simon Cho on 06/05/2012

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: