Friday, October 29, 2010

Date and Time Conversions from int using dbo.sysjobhistory

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:

CONVERT(char(100), SERVERPROPERTY('Servername')) AS hostname,
@@SERVICENAME As instance,

((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,
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 as "job name",
CASE WHEN run_status = '0' THEN message
WHEN run_status = '0' THEN ''
END AS "error message",
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,
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 like '%Backup%'
--Order by Start Time Descending
((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