Consuming SQL Agent dates & durations

SQL Agent’s schema is older than me. It handles dates, times, and durations like it’s 1980 by using integers instead of date/time data types. My buddy Aaron Bertrand talks more about Dating Responsibly so that you can have a good datetime with your own database.

I was writing a query to pull recent job failures from SQL Agent’s msdb job history, and knew that I didn’t want to deal with the wonky date/time formats. Specifically, I was querying msdb.dbo.sysjobhistory to find the Start Time, End Time, and Duration of job runs that failed. If you aren’t familiar with that table, you can look at it over in the docs.

SQL Server does ship with an (undocumented) scalar function that will combine the run_date and run_time columns into a datetime value. That scalar function is dbo.agent_datetime(). Unfortunately, that only got me part way there, since it doesn’t help me decode the duration, which is necessary to compute the job finish time. Also, it’s a scalar function, which is just kinda icky. Scalar functions are ironically named, because they don’t scale well to large result sets.

Instead, I decided to craft my own inline table-valued function (TVF). TVFs give better performance by keeping the operation set-based, and it would let me handle the duration as well. Once I wrote my TVF, my query of the job history couldn’t have been easier:

SELECT j.name, h.step_id, h.message, t.* 
FROM msdb.dbo.sysjobs j 
JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
CROSS APPLY dbo.AgentJobHistory_TimeConvert (h.run_date, h.run_time, h.run_duration) t
WHERE h.step_id = 0 --only the final job status, I don't care about individual steps
AND   h.run_status NOT IN (1,4) --ignore successful & in-progress runs

How’d you do that, Andy?!?

It’s not magic… It’s just math. Agent stores dates like YYYYMMDD, and both the time & duration in the format hh24miss. It’s just a matter of slicing all those pieces apart, and using DATETIMEFROMPARTS() to mush them back together. For Duration, we slice them apart, convert everything to seconds and add it up.

TBH, it’s Friday afternoon at 4pm, so I’m going to wrap it up instead of accidentally launching into a whole complicated math course. I’m going to let you go check out the code for yourself. You can see this function and the rest of my DBA database over on GitHub.