SELECT *, datediff(second, start_datetime, End_DateTime ) AS timetaken FROM (
SELECT j.Name, jh.Step_name, jh.run_date as run_date,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) +
((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 ) / (23.999999*3600)
AS Start_DateTime, CONVERT(DATETIME, RTRIM(jh.run_date)) +
((jh.run_time/10000 * 3600) +
((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */) +
((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) +
(jh.run_duration%10000)%100) / (86399.9964) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
and j.name = 'zzzz'
and step_name = 'xxxx'
) t1
ORDER BY run_date desc
You'll need to substitute out zzzz for the name of the job, and xxxx for the name of the step (if required)
No comments:
Post a Comment