About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Thursday, September 1, 2011

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




No comments:

Post a Comment