Find SQL scheduled jobs to run during a specific time range

There are times when we need to know which jobs are going to run in the upcoming half an hour or so. It is quite possible and easy when you invoke the job activity monitor. However, if you want to know through a query or if you want to make a report, here is the code.

SELECT j.name, dbo.agent_datetime(next_run_date, next_run_time),
STUFF(STUFF(RIGHT(replicate('0', 6) +  CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':'),
STUFF(STUFF(RIGHT(replicate('0', 6) +  CAST(active_end_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':'),
              CASE
               WHEN freq_type = 4 THEN 'Daily'
               WHEN freq_type = 8 THEN 'Weekly'
               WHEN freq_type = 16 THEN 'Monthly'
               WHEN freq_type = 32 THEN 'Monthly'
              END frequency,
              'every ' + CAST (freq_interval AS VARCHAR(3)) + ' day(s)'  Days,
              CASE
               WHEN freq_subday_type = 2
               THEN ' every ' + CAST(freq_subday_interval AS VARCHAR(7)) + ' seconds'
               WHEN freq_subday_type = 4
               THEN ' every ' + CAST(freq_subday_interval AS VARCHAR(7)) + ' minutes'
               WHEN freq_subday_type = 8
               THEN ' every ' + CAST(freq_subday_interval AS VARCHAR(7)) + ' hours'
               else ' starting at '
               +STUFF(STUFF(RIGHT(replicate('0', 6) + 
CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')             END time
FROM msdb..sysjobschedules S
JOIN msdb..sysjobs J ON S.job_id = J.job_id
JOIN msdb..sysschedules sc ON sc.schedule_id = s.schedule_id
WHERE CONVERT(VARCHAR(10), dbo.agent_datetime(next_run_date, next_run_time),101)= CONVERT(VARCHAR(10), GETDATE(),101)
AND next_run_date > 0 AND next_run_time > 0
AND CONVERT(VARCHAR(5),dbo.agent_datetime(next_run_date,next_run_time),114)>='16:00'
AND CONVERT(VARCHAR(5),dbo.agent_datetime(next_run_date,next_run_time),114)<='20:30'
AND j.enabled = 1
ORDER BY dbo.agent_datetime(next_run_date, next_run_time) 

This will return the data in the below format.

 

Name: Name of the job

Next run date-time: Next occurrence of the job that falls between the time frame mentioned in the query i.e., 16:00 to 20:30 hours.

Active Start Time: Job starting point in the day.

Active End Time: Job ending point in the day.

Frequency: Daily/Weekly/Monthly

Days: Scheduled days (every day, every 2 days, every x number of days, etc.)

Time: Interval between each occurrence.