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)
*/