Convert the run-date, run_time, and run_duration of a SQL Server job to start and stop times

The below code will allow you to view the start and stop times of SQL Server jobs

SELECT

j.name,

jh.step_name,

jh.message,

jh.server,

t.start_time,

DATEADD(second, t.duration_seconds, t.start_time) as end_time,

t.duration_seconds

FROM

(      SELECT

       instance_id,

       CAST(

       --get the date

       SUBSTRING(CAST(run_date as VARCHAR), 5, 2) + '/' +

       SUBSTRING(CAST(run_date as VARCHAR), 7, 2) + '/' +

       SUBSTRING(CAST(run_date as VARCHAR), 1, 4) + ' ' +

       --get the time

       SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR), 6), 1, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR), 6), 3, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR), 6), 5, 2) as SMALLDATETIME) as start_time,

       --get duration in seconds

       SUBSTRING(RIGHT('000000' + CAST(run_duration as VARCHAR), 6), 1, 2) * (60 * 60) +

       SUBSTRING(RIGHT('000000' + CAST(run_duration as VARCHAR), 6), 3, 2) * (60) +

       SUBSTRING(RIGHT('000000' + CAST(run_duration as VARCHAR), 6), 5, 2) as duration_seconds

       FROM msdb.dbo.sysjobhistory) t

INNER JOIN msdb.dbo.sysjobhistory jh on t.instance_id = jh.instance_id

INNER JOIN msdb.dbo.sysjobs j on j.job_id = jh.job_id

ORDER BY t.start_time desc