Generate SQL Job Schedule Description

I was actually calling the system stored procedure "msdb.dbo.sp_help_jobschedule" in my previous post about modifying column list from a called stored procedure,

While working on that I noticed that [more]the description (schedule_description) being returned by stored procedure (if you set @include_description = 1) returns date and times as integer values (rather than date/time format).

While looking around I found this feedback from microsoft connect : sp_get_schedule_description produces basic output, be nice if it had formatted dates & times (you might be required to login to view it)

Turns out the behavior was changed from SQL2000 to SQL2005 and won't be changed(or corrected should I say) since other applications that depended on the format might break.

So I figured I'd have a function/method for returning a more user friendly description instead.

Found this article as a baseline: Generate Job Schedule Description and created the functions below (GetScheduleDescription and GetTimeString)

— START

IF EXISTS (
  SELECT *
  FROM sys.objects
  WHERE
    object_id = OBJECT_ID(N'[dbo].[GetTimeString]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetTimeString]

GO

— =============================================
— Author: Ryan Garaygay
— Create date: 03/25/2008
— Description:    Return Time String Representation from INT (scheduled job format, HHmmss)
— =============================================
CREATE FUNCTION [dbo].[GetTimeString](@timeInteger INT)
RETURNS NVARCHAR(255)
AS
BEGIN

— convert to 12hr time
DECLARE @originalTimeInteger INT
SET @originalTimeInteger = @timeInteger
IF(@timeInteger >= 130000)
  SET @timeInteger = @timeInteger – 120000

— convert to 6 chars (pad if necessary)
DECLARE @TM VARCHAR(255)
SET @TM = REPLACE(STR(@timeInteger, 6), SPACE(1), '0')

DECLARE @RETVAL VARCHAR(255)
SET @RETVAL =
  SUBSTRING(@TM,1,2) + ':' +
  SUBSTRING(@TM,3,2) + ':' +
  SUBSTRING(@TM,5,2)
  + CASE
    WHEN @originalTimeInteger < 120000 THEN ' AM'
    ELSE ' PM'
  END
  RETURN @RETVAL
END
GO

IF EXISTS (
  SELECT *
  FROM sys.objects
  WHERE
    object_id = OBJECT_ID(N'[dbo].[GetScheduleDescription]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetScheduleDescription]

GO

— =============================================
— Author: Ryan Garaygay
— Create date: 03/25/2008
— Description:    Return Schedule Description
— a modification of sp_get_schedule_description
— but there is an issue with SQL2005 where the date/times returned are in INT
— instead of recognizable date/time formats
— =============================================
CREATE FUNCTION [dbo].[GetScheduleDescription](
  @freq_type              INT          = NULL,
  @freq_interval          INT          = NULL,
  @freq_subday_type       INT          = NULL,
  @freq_subday_interval   INT          = NULL,
  @freq_relative_interval INT          = NULL,
  @freq_recurrence_factor INT          = NULL,
  @active_start_date      INT          = NULL,
  @active_end_date        INT          = NULL,
  @active_start_time      INT          = NULL,
  @active_end_time        INT          = NULL,
  @enabled                BIT          = NULL)
RETURNS NVARCHAR(255)
AS
BEGIN
  DECLARE @schedule_description NVARCHAR(255)
  SET @schedule_description =
  'Occurs ' +
  CASE
    WHEN @freq_type = 0x1 — OneTime
      THEN
        'once on '
        + CONVERT(
            CHAR(10)
            , CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
            , 101 — mm/dd/yyyy
          )
    WHEN @freq_type = 0x4 — Daily
      THEN
        CASE
          WHEN @freq_interval = 1
            THEN 'daily '
          WHEN @freq_interval > 1
            THEN 'every '
            + CAST( @freq_interval AS VARCHAR )
            + ' days '
        END
    WHEN @freq_type = 0x8 — weekly
      THEN
        CASE
          WHEN @freq_recurrence_factor = 1
            THEN 'weekly on '
          WHEN @freq_recurrence_factor > 1
            THEN 'every '
            + CAST( @freq_recurrence_factor AS VARCHAR )
            + ' weeks on '
        END
        + LEFT(
             CASE WHEN @freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
           + CASE WHEN @freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
           + CASE WHEN @freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
           + CASE WHEN @freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
           + CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
           + CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
           + CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
         , LEN(
                    CASE WHEN @freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END
                  + CASE WHEN @freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END
                  + CASE WHEN @freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END
                  + CASE WHEN @freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END
                  + CASE WHEN @freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END
                  + CASE WHEN @freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END
                  + CASE WHEN @freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END
              ) – 1  — LEN() ignores trailing spaces
        )
     WHEN @freq_type = 0x10 — monthly
         THEN
             CASE
                 WHEN @freq_recurrence_factor = 1
                     THEN 'monthly on the '
                 WHEN @freq_recurrence_factor > 1
                     THEN 'every '
                        + CAST( @freq_recurrence_factor AS VARCHAR )
                        + ' months on the '
             END
           + CAST( @freq_interval AS VARCHAR )
           + CASE
                 WHEN @freq_interval IN ( 1, 21, 31 ) THEN 'st'
                 WHEN @freq_interval IN ( 2, 22     ) THEN 'nd'
                 WHEN @freq_interval IN ( 3, 23     ) THEN 'rd'
                 ELSE 'th'
             END
     WHEN @freq_type = 0x20 — monthly relative
         THEN
             CASE
                 WHEN @freq_recurrence_factor = 1
                     THEN 'monthly on the '
                 WHEN @freq_recurrence_factor > 1
                     THEN 'every '
                        + CAST( @freq_recurrence_factor AS VARCHAR )
                        + ' months on the '
             END
           + CASE @freq_relative_interval
                 WHEN 0x01 THEN 'first '
                 WHEN 0x02 THEN 'second '
                 WHEN 0x04 THEN 'third '
                 WHEN 0x08 THEN 'fourth '
                 WHEN 0x10 THEN 'last '
             END
           + CASE @freq_interval
                 WHEN  1 THEN 'Sunday'
                 WHEN  2 THEN 'Monday'
                 WHEN  3 THEN 'Tuesday'
                 WHEN  4 THEN 'Wednesday'
                 WHEN  5 THEN 'Thursday'
                 WHEN  6 THEN 'Friday'
                 WHEN  7 THEN 'Saturday'
                 WHEN  8 THEN 'day'
                 WHEN  9 THEN 'week day'
                 WHEN 10 THEN 'weekend day'
             END
     WHEN @freq_type = 0x40
         THEN 'Automatically starts when SQLServerAgent start@'
     WHEN @freq_type = 0x80
         THEN 'Starts whenever the CPUs become idle'
     ELSE ''
   END
 + CASE
     WHEN @freq_subday_type = 0x1 OR @freq_type = 0x1
         THEN ' at '
           + dbo.[GetTimeString](@active_start_time)
     WHEN @freq_subday_type IN ( 0x2, 0x4, 0x8 )
         THEN ' running every '
           + CAST( @freq_subday_interval AS VARCHAR )
           + CASE @freq_subday_type
                 WHEN 0x2 THEN ' second'
                 WHEN 0x4 THEN ' minute'
                 WHEN 0x8 THEN ' hour'
             END
           + CASE
                 WHEN @freq_subday_interval > 1 THEN 's'
                 ELSE ''
             END
            + ' between '
            + dbo.[GetTimeString](@active_start_time)
            + ' and '
            + dbo.[GetTimeString](@active_end_time)
     ELSE ''
   END
 + CASE
     WHEN @freq_type IN ( 4, 8, 16, 32 ) — daily, weekly, monthly and monthly relative
         THEN '. Schedule will be used '
          +
            CASE
              WHEN (@active_end_date IS NULL OR @active_end_date = 99991231)
                THEN ' starting '
                  + CONVERT(
                    CHAR(10)
                    , CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
                    , 101 — mm/dd/yyyy
                  )
                  + '.'
              ELSE
                ' between '
                  + CONVERT(
                    CHAR(10)
                    , CAST( CAST( @active_start_date AS VARCHAR ) AS DATETIME )
                    , 101 — mm/dd/yyyy
                  )
                  + ' and '
                  + CONVERT(
                    CHAR(10)
                    , CAST( CAST( @active_end_date AS VARCHAR ) AS DATETIME )
                    , 101 — mm/dd/yyyy
                  )
                  + '.'
            END
     ELSE ''
   END

  RETURN @schedule_description
END

GO

IF EXISTS (
  SELECT *
  FROM sys.objects
  WHERE
    object_id = OBJECT_ID(N'[dbo].[CM1_SEL_ScheduleByJobID]')
    AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CM1_SEL_ScheduleByJobID]

— END

Again, as previously mentioned, used INSERT-EXEC to simply store the values of my "msdb.dbo.sp_help_jobschedule" query to a table variable. Then for the final SELECT statement, I called my new GetScheduleDescription

      [ScheduleDescription] = dbo.GetScheduleDescription(
          freq_type,
          freq_interval,
          freq_subday_type,
          freq_subday_interval,
          freq_relative_interval,
          freq_recurrence_factor,
          active_start_date,
          active_end_date,
          active_start_time,
          active_end_time,
          enabled)

Hope this helps anyone. 

REVISED as of 3/27/08 – handling of 12:mm PM and changes to further enhance the description returned.