http://www.sqlservercentral.com/blogs/briankmcdonald/2010/10/29/sqlbigeek_1920_s-function-friday-_1320_-convert-job-duration-to-seconds/

Printed 2014/08/21 10:37AM

SQLBIGeek’s Function Friday – Convert Job Duration to Seconds

By Brian K. McDonald, 2010/10/29

 

 

 

Brian K. McDonald

Brian K. McDonald

SQLBIGeek

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

GO

 

CREATE FUNCTION dbo.ufn_JobIntToSeconds

(

      @run_duration INT

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

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

Email:      bmcdonald@SQLBIGeek.com

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

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

 

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

)

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

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald

 

 


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