udfFormatTimeWithColons()

This user-defined scalar function is helpful in properly formatting column next_run_time from table msdb.dbo.sysJobSchedules. 

Use Admin

go

IF object_id('dbo.udfFormatTimeWithColons') Is Not Null

      DROP FUNCTION dbo.udfFormatTimeWithColons

go

CREATE FUNCTION dbo.udfFormatTimeWithColons(

      @In varchar(6)

      )

RETURNS varchar(8)

AS

/*    DATE        AUTHOR            REMARKS

      9/1/11            PPaiva            Initial creation.

      DESCRIPTION

            Helpful for formatting the next_run_time column

                  in msdb.dbo.sysJobSchedules.

                             

             Input:  073000

            Output:  07:30:00

     

      USAGE

            SELECT dbo.udfFormatTimeWithColons('073000')   

           

      DEBUG

            SELECT *

            FROM msdb.dbo.sysJobSchedules

            SELECT *, dbo.udfFormatTimeWithColons(next_run_time)

            FROM msdb.dbo.sysJobSchedules

           

           

*/

BEGIN

      DECLARE @Out varchar(8)

      SET @Out =        Left(@In, 2)

                        + ':'

                        +     Substring(@In, 3, 2)

                        + ':'

                        +     Right(@In, 2)

      RETURN @Out

END