SQL SERVER – Find Next Running Time of Scheduled Job Using T-SQL

December 22, 2008 by pinaldave

I often receive good question on blog, however, I do not always receive good answer for the questions. Imran Mohammed is one SQL Expert who spend his time helping SQL Community on this blog. I always read his answers as there is something to learn every time. Recently someone asked on blog about Finding next run time for Schedule Job using T-SQL. Imran came up with nice script. I have modified it a bit to adjust needs.

USE msdb ;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date FROM sysjobschedules) SELECT A.name Job_Name, 'Will be running today at '+ SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12 THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12 ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At' FROM sysjobs A ,CTE B WHERE A.job_id = B.job_id AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+ SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+ SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101) AND (SUBSTRING( CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12 THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12 ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2) +':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) > SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)

Reference : Pinal Dave (http://blog.SQLAuthority.com), Imran Mohammed

Posted in Pinal Dave, Readers Contribution, SQL, SQL Add-On, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged Scheduled Jobs, SQL Server Agent | 6 Comments

6 Responses

on December 22, 2008 at 7:09 am | Reply Imran Mohammed

Hello Pinal,

Thanks for publishing this comment.

I remember the question was something like this,

I wanted to know the jobs that are scheduled for today and not yet executed.

So I wrote this script in a way that it will check for todays job and also it will check for time, say its 4 PM now… it will list all the jobs that are scheduled to run after 4 PM… ( today ), it will not list those jobs before 4 PM ( in our example)

I wrote this script in hurry as I was leaving to home from my job… so I did not check it completely… Thanks for fixing it.

Thanks
IM.
on December 22, 2008 at 9:10 pm | Reply Derek W

When I ran it, I received this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘‘’.
on December 22, 2008 at 11:52 pm | Reply Bobby Rentfro

1. Replace all the single quotes after copying and pasting the code. Mine always replace the single quotes with another character for some reason.

2. Replace the CTE in the from clause with an embedded subselect:
FROM sysjobs A ,(SELECT schedule_id, job_id, RIGHT(‘0′+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules) CTE

3. Comment out lines 2 and 3

4. Run the script.
on December 23, 2008 at 1:51 am | Reply Jerry Hung

Cleaned it up a bit, it’s useful to have for knowing what the upcoming jobs are without worrying about all the details (frequency, enabled, etc…) where they are many scripts available as well

————–
USE msdb

;WITH CTE AS (
SELECT schedule_id, job_id, RIGHT(‘0′+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name
–, next_run_date, next_run_time
,’Will be running today at ‘+
Stuff(Stuff(right(‘000000′+Cast(next_run_time as Varchar),6),3,0,’:'),6,0,’:') ‘ScheduledAt’
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND cast(cast(next_run_date as varchar(15)) as datetime) = CONVERT(VARCHAR(10),GETDATE(),101) – same date
AND next_run_time > REPLACE(SUBSTRING(CONVERT( VARCHAR(30) , GETDATE(),120),12,10),’:',”) – compare time
ORDER BY ScheduledAt ASC
on January 4, 2009 at 6:38 am | Reply guhPuntee

Hi all!

My name is James and I’m new here :). So far this is an incredible place for information and I have spent a ton of time reading and browsing around. Look forward to hearing from you!
on November 18, 2009 at 2:03 pm | Reply Brembot

Is there a way to know that the scheduled jobs are NOT running on a specified time schedule? I encounter this one once we restarted the server and the jobs schedule missed.