A few functions for working with SQL Agent Job data

If you’ve ever looked at sysjobhistory, you know that the way SQL Server stores datetimes and durations in this table are not very friendly. Well, that’s not quite true. They are human-readable if they are small, but if you’ve got a job that runs for hours (days?), it becomes more and more difficult.
For example, a job which started at 9:05AM today (5/9/2011) would have a run_time of 905 and a run_date of 20110905. Both of those are recognizable, but since they are integers, it’s complicated to get them into a datetime variable in PowerShell in order to manipulate them. I have seen several posts on how to create a UDF (user-defined function) to convert these values to a datetime, and that’s usually a good solution. Unfortunately, it’s not always possible (politically) to create a UDF on a SQL instance. For example, if you are simply monitoring a box to make sure that backups are run, and don’t have much control over the box besides that function, you might not be allowed to create the necessary UDFs. Similarly, a SQL solution involving a select statement that kneads the data into the value you want is not a great solution because it’s not reusable, except in the cut-paste sense.
For that reason, I have written 4 different functions to assist with managing job data.

function convertfrom-SqlAgentDuration{
    return New-TimeSpan -seconds ($sql_duration % 100) -minutes (($sql_duration / 100) % 100) -hours ($sql_duration /10000)

function convertfrom-SQLAgentDateTime{
    return (get-date -year ($sql_startdate/10000) -month ($sql_startdate/100 % 100) -day ($sql_startdate %100) -hour ($sql_starttime/10000 % 100) -minute ($sql_starttime/100 % 100) -sec ($sql_starttime % 100))

function convertto-SQLAgentDuration{
    return $duration.seconds+100*$duration.minutes+10000*($duration.hours+24*$duration.days)

function convertto-SQLAgentDateTime{
    return (new-object PSObject -property @{date=[int](get-date $date -uformat "%Y%m%d");time=[int](get-date $date -uformat "%H%M%S")})

Obviously, these are not high-performance functions, but given the volume of data you would be getting out of the SQL Agent, that shouldn’t pose any kind of problem. Here are some example usages:


Monday, May 09, 2011 9:01:58 PM
PS>convertto-sqlagentdatetime (get-date) | ft -auto

    date   time
    ----   ----
20110509 210648
PS>convertfrom-sqlagentdatetime 20110509 210648

Monday, May 09, 2011 9:06:48 PM
PS>convertfrom-SqlAgentDuration 12345

Days              : 0
Hours             : 1
Minutes           : 23
Seconds           : 45
Milliseconds      : 0
Ticks             : 50250000000
TotalDays         : 0.0581597222222222
TotalHours        : 1.39583333333333
TotalMinutes      : 83.75
TotalSeconds      : 5025
TotalMilliseconds : 5025000

I hope these functions are useful to you. Let me know what you think.