udfFormatJobNextRunDate - Properly format a job's next run date/time

Scenario

You want to quickly determine the next jobs to be executed on a busy server.  Perhaps you need to bring the server down and you want to ensure you don't miss any important jobs.  Or you are managing resources for the next couple of hours and you need to know which jobs to disable.

1^st Solution

Your first approach is to query sysjobs and sysschedules.

-- Shows list of jobs and when they next run

SELECT  j.name JobName,

            j.Enabled,

            j.Description,

            s.next_run_date,

            s.next_run_time

FROM msdb.dbo.sysjobs j

LEFT JOIN msdb.dbo.sysjobschedules s

      ON s.job_id = j.job_id

ORDER BY next_run_date desc, next_run_time desc

Alas, while the above query gets you everything you need, the next TIME for when a job is scheduled to run is formatted poorly.  Therefore the ordering is incorrect.

Note the poorly formatted time below.  Ordering by next_run_time will not yield correct results.  I added an additional column showing what the correctly formatted date/time should be.

Solution for formatting next run date and time of jobs using a user-defined function

Use Admin

-- Use master

IF Object_ID('dbo.udfFormatJobNextRunDate') Is Not Null

      DROP FUNCTION dbo.udfFormatJobNextRunDate

go

CREATE FUNCTION dbo.udfFormatJobNextRunDate(

      @NextRunDateTEXT varchar(8),

      @NextRunTimeTEXT varchar(6)

      )

RETURNS Datetime

AS

/*    DATE        AUTHOR            REMARKS

      10/19/15    PPaiva            Initial creation

      DESCRIPTION

            Formats the text columns Next_Run_Date and Next_Run_Time

                  in sysJobSchedules to a datetime format.

            TIME formatting

                   Input:  4000

                  Output:  00:40:00

      USAGE

            SELECT  *, 

              dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) ProperlyFormattedDatetime

            FROM msdb.dbo.sysJobSchedules

            ORDER BY len(next_run_time)

           

            SELECT dbo.udfFormatJobNextRunDate('20150401', '300')

            SELECT dbo.udfFormatJobNextRunDate('20150401', '2300')

            SELECT dbo.udfFormatJobNextRunDate('20150401', '12300')

            SELECT dbo.udfFormatJobNextRunDate('20150401', '112300')

*/

BEGIN

      DECLARE @Out datetime,

                  @NextRunTimeWithColons varchar(8),

                  @LenTime int

     

                 

      -- Pad left with zeros

      SET @LenTime = Len(@NextRunTimeTEXT)

      SET @NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT

      -- Add colons.  Change 005000 to 00:50:00

      SET @NextRunTimeWithColons = Left(@NextRunTimeTEXT, 2)

                  + ':'

                  +     Substring(@NextRunTimeTEXT, 3, 2)

                  + ':'

                  +     Right(@NextRunTimeTEXT, 2)

      -- Concatenate date and time

      SET @Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null

                              ELSE Convert(datetime, @NextRunDateTEXT + ' ' + @NextRunTimeWithColons

                                                )

                                    END

      RETURN @Out

END

2^nd Solution

Now simply add the new function to the initial query.

-- Shows list of jobs and when they next run (modified with solution)

-- Now jobs are correctly order by next run date/time

SELECT  j.name JobName,

            j.Enabled,

            j.Description,

            s.next_run_date,

            s.next_run_time,

            dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) CorrectlyFormattedDatetime

FROM msdb.dbo.sysjobs j

LEFT JOIN msdb.dbo.sysjobschedules s

      ON s.job_id = j.job_id

ORDER BY 5 desc, 6 desc