Convert MSDB Job Duration to Seconds

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)

*/