SQL Agent Job Durations As TimeSpans

Rob Sewell shows us how to convert the SQL Agent job duration into a .NET TimeSpan using Powershell:

The first job took 15 hours 41 minutes  53 seconds, the second 1 minute 25 seconds, the third 21 seconds. This makes it quite tricky to calculate the duration in a suitable datatype. In T-SQL people use scripts like the following from MSSQLTips.com

((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)  as ‘RunDurationMinutes’

I wish that some version of SQL Server would fix this “clever” duration.  We’ve had the time datatype since 2008; at least add a new column with run duration as a time value if you’re that concerned with backwards compatibility.

Related Posts

Publishing DACPACs With Powershell

Richie Lee has a new set of functions for dbatools: There are 3 modules included. I’m going to write a bigger piece about these functions elsewhere, but for now here is a link to the documentation – Export-DbaDacpac New-DbaPublishProfile Publish-DbaDacpac Read on for more information and a link to the source code if you’re interested […]

Read More

String Formatting With Powershell

Thomas Raynor provides a quick tip for string formatting in Powershell: And, for some reason, instead of the default output which is formatted like a table, I want output presented like this. 1 2 3 .ps1     file extension: 11 .xlsx    file extension: 3 .dll     file extension: 1 This is a silly example, but notice that even […]

Read More


September 2016
« Aug Oct »