Printed 2016/06/01 02:13AM

SQLBIGeek’s Function Friday – Convert Job Duration to Seconds

By Brian K. McDonald, 2010/10/29




Brian K. McDonald

Brian K. McDonald


Twitter: @briankmcdonald


Welcome to the second post of my “SQLBIGeek’s Function Friday” blog series. In this series, I am hoping that I can provide some of the details about functions that I have either found on the internet or something that I had to create to meet a need. If it is something that I come across, I will attempt to make it shorter and/or perform better. If it is something I wrote, it is just something to share with you! J Either way, I hope that you enjoy the series and that you can get some useful ideas or functions to use in your environment.



A few weeks ago, I posted a blog about how SQL Server stores the duration of job execution in a strange format. I showed you one way to determine hours, minutes and seconds in a little bit easier to read format than what they provide. Here, is a function that you can use determine those values in seconds.


Script 1: Function

USE AdventureWorks



CREATE FUNCTION dbo.ufn_JobIntToSeconds


      @run_duration INT


Created By: Brian K. McDonald, MCDBA, MCSD (


Twitter:    @briankmcdonald

Date:       10/29/2010

Purpose:    Convert the duration of a job to seconds

            A value of 13210 would be 1 hour, 32 minutes and 10 seconds,

            but I want to return this value in seconds. Which is 5530!

            Then I can sum all of the values and to find total duration.


Usage:      SELECT dbo.ufn_JobIntToSeconds (13210)


Modification History











            --hours, minutes and seconds

            WHEN LEN(@run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(@run_duration,LEN(@run_duration)-4)) * 3600

             + LEFT(RIGHT(@run_duration,4),2) * 60 + RIGHT(@run_duration,2)

            --minutes and seconds

            WHEN LEN(@run_duration) = 4 THEN LEFT(@run_duration,2) * 60 + RIGHT(@run_duration,2)

            WHEN LEN(@run_duration) = 3 THEN LEFT(@run_duration,1) * 60 + RIGHT(@run_duration,2)

      ELSE --only seconds    






--Execute it a few times

SELECT dbo.ufn_JobIntToSeconds(125)       --85

SELECT dbo.ufn_JobIntToSeconds(13210)     --5530

SELECT dbo.ufn_JobIntToSeconds(2210)      --1330


--Clean up if you want

DROP FUNCTION dbo.ufn_JobIntToSeconds


As a usable scenario, now you can query the sysjobhistory table and determine the duration of each step with a similar query to that shown in script 2.


Script 2: Query SysJobHistory

SELECT dbo.ufn_JobIntToSeconds(run_duration) AS [Seconds]

      , dbo.ufn_JobIntToSeconds(run_duration)/60 AS [Minutes]

FROM msdb.dbo.sysjobhistory


Please be sure to return for the next BISQLGeek's Function Friday! I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! I would love to hear your comments. Please let me know if you’ve ever had to overcome this kind of issue in your environment.


Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.



Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn:



Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.