Convert MSDB Job Duration to Seconds

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 27 Oct 2010

Description

As previously described in the tip Convert MSDB Date and Time INTEGERS to DATETIME , MSDB date and time storage does not use TSQL DATETIME data type.

Similarly, the storage of a job duration in the MSDB job history tables also uses an integer of the form HHMMSS.

This function converts the integer to an integer number of seconds which can then be used with any of the standard date functions eg. to produce a job end time or formatted duration string.

Code

DROP FUNCTION [dbo].[udf_ConvertMaintPlanDurationToSeconds]

GO

CREATE FUNCTION [dbo].[udf_ConvertMaintPlanDurationToSeconds] (@Duration INT ) RETURNS INT

AS

/******************************************************************************

Purpose: Converts the very unfriendly Maint Plan duration integer to

a number of seconds (the lowest granularity of the integer representation).

This can then be used in any standard date function

NOTE: Maint Plan duration ttored as an Integer reperesenting HHMMSS

Author: ChillyDBA

History: 27 Oct 2010 - Initial Issue

*******************************************************************************/

BEGIN

DECLARE

@DurationText VARCHAR(6),

@DurationDateTime DATETIME,

@ReturnInt INT

-- first, convert the integers to strings, complete with leading zeroes

SELECT

@DurationText = RIGHT(CAST('000000' AS VARCHAR(12)) + CAST(@Duration AS VARCHAR(12)), 6)

-- now do the conversion

SELECT @DurationDateTime = CAST

(

'1 Jan 1900 ' +

SUBSTRING(@DurationText, 1,2) +

':' +

SUBSTRING(@DurationText, 3,2) +

':' +

SUBSTRING(@DurationText, 5,2)

AS DATETIME

)

SELECT @ReturnInt = DATEDIFF(SS, '1 Jan 1900', @DurationDateTime)

RETURN @ReturnInt

END

/*

SELECT [dbo].[udf_ConvertMaintPlanDurationToSeconds] (94411)

SELECT [dbo].[udf_ConvertMaintPlanDurationToSeconds] (104411)

SELECT [dbo].[udf_ConvertMaintPlanDurationToSeconds] (11)

SELECT [dbo].[udf_ConvertMaintPlanDurationToSeconds] (111)

*/