SQL Server 2008 (SSMS) : Find next run date for a job

Question

I would like to know if there is a way to get the next time a job is supposed to be running in SQL Server 2008, using a T-SQL query or even in SSMS if possible, without having to consult all the schedules for all the jobs.

Thank you

Answer 1

Run sp_help_job in the msdb database. The next_run_date and next_run_time columns have the values you are looking for.

Answer 2

This'll get you a single row result set with your job name and the next run date/time.

DECLARE @JobName sysname
SET @JobName='Query Tool Daily Routines'

SELECT
    JobName,
    MAX(NextRunTime) as NextRunTime
FROM (
    SELECT 
        j.name as JobName,
        cast(
            CONVERT(CHAR(8), next_run_date, 112) 
            + ' ' 
            + STUFF(STUFF(RIGHT('000000' 
            + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':')
            as datetime) as NextRunTime
    FROM msdb.dbo.sysjobs j
    join msdb.dbo.sysjobschedules s on j.job_id = s.job_id
        and j.name=@JobName
) t1
group by JobName

You can of course get rid of the DECLARE and SET and just include it in the join of the inner query.