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)