SQL Job history – Getting end time from sysjobhistory

Run_date, run_time, run_duration are stored in int format in sysjobhistory table, not user friendly. For a quick solution I had to pull the history from last X hours.

This simple TSQL helped to find end time of the step!


select top 10000 instance_id, job_name = sj.name, run_date, run_time, run_duration 
--,convert(DATETIME,RTRIM(run_date)) as myrundate 
--,convert(int,(run_time / 10000)) as run_time_hour 
--,convert(int,(run_time / 100))%100 as run_time_minutes 
--,convert(int,(run_time / 1))%100 as run_time_seconds 
--,convert(int,(run_duration / 1000000)) as run_duration_days
--,convert(int,(run_duration / 10000))%100 as run_duration_hour 
--,convert(int,(run_duration / 100))%100 as run_duration_minutes 
--,convert(int,(run_duration / 1))%100 as run_duration_seconds 
, dateadd(second,  ( convert(int,(run_time / 10000)) * 3600 )
+ (convert(int,(run_time / 100))%100) * 60 
+  (convert(int,(run_time / 1))%100)
+ ( convert(int,(run_duration / 1000000))) * 3600 * 24
+  (convert(int,(run_duration / 10000))%100) * 3600
+ (convert(int,(run_duration / 100))%100) * 60
+ (convert(int,(run_duration / 1))%100)
,convert(DATETIME,RTRIM(run_date))) as StepCompleteDateTime
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id

This entry was posted in Productivity, SQL, SQL Server, Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s