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


ċ
udf_ConvertMaintPlanDurationToSeconds.sql
(2k)
Andy Hughes,
Jun 22, 2012, 11:49 AM
Comments