BrianKMcDonald

SQLBIGeek’s Function Friday – Convert Job Duration to Seconds

 Brian K. McDonaldSQLBIGeekTwitter: @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

GO

CREATE FUNCTION dbo.ufn_JobIntToSeconds

(

@run_duration INT

/*=========================================================================

Created By: Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)

Email:      bmcdonald@SQLBIGeek.com

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

----------------------------------------------------------------------------

==========================================================================*/

)

RETURNS INT

AS

BEGIN

RETURN

CASE

--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

RIGHT(@run_duration,2)

END

END

GO

--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: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Posted by Carl Federl on 30 October 2010

Instead of string manipulation, an alternative algorithm using Modulus and integer division is:

RETURN

-- seconds

( (@HHMMSSInteger %       100 ) )

-- minutes

+ ( (@HHMMSSInteger %     10000 ) /     100 ) * 60

-- hours

+ ( (@HHMMSSInteger %   1000000 ) /   10000 ) * 60 * 60

Posted by Anonymous on 3 November 2010

Pingback from  Roman&#039;s Blog

Posted by alen on 3 November 2010

i actually created a process a few months ago to dump the data into a central database and to report failed jobs in the last day and the fact that the data in msdb was in int or whatever non-date format it was in drove me crazy

Posted by Brian K. McDonald on 3 November 2010

Great! I'm glad I could provide something that you can use. Please let me know if there is something you are looking for and I'll get cracking on it. :)

Posted by Anonymous on 1 December 2010

Pingback from  RealTime - Questions: "Software returns weird date value - What is it ?"