I was charged with developing a T-SQL query to list backup job histories, found in dbo.sysjobs and dbo.sysjobhistory. One of the requirements was to have the end date of the job. In the dbo.sysjobhistories table, there is an integer start date (run_date), time (run_time) and duration (run_duration) field, but no end date or end time data. I came across some code to create those end date and end time fields, plus convert them and the others into a usable datetime field.
Anyway, if you need a way to track backup job histories or even make it into a view, here is some helpful code:
SELECT
--Server
CONVERT(char(100), SERVERPROPERTY('Servername')) AS hostname,
--Instance
@@SERVICENAME As instance,
CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS startdatetime,
--EndDate
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS enddatetime,
--Job Name
sj.name as "job name",
--Error
CASE WHEN run_status = '0' THEN message
WHEN run_status = '0' THEN ''
END AS "error message",
--Duration
LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) as duration,
--Status
CASE WHEN run_status = '1' THEN 'Success'
WHEN run_status = '0' THEN 'Failure'
END AS "status"
FROM dbo.sysjobs sj
INNER JOIN dbo.sysjobhistory sh on sh.job_id = sj.job_id
WHERE sh.step_id = '1'
--Backups Only
AND sj.name like '%Backup%'
--Order by Start Time Descending
ORDER BY CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) DESC
