SQL: Get next run time for SQL server jobs

SQL Server Manager GUI doesn’t show you next run time when you look at job schedule (at least in SQL Server 2008 R2) so you may use this to get it:

\n\n\n[code lang=”sql”]\nSUBSTRING(CONVERT(VARCHAR(10),CASEWHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)> 12\n\nTHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time),1,2)-12\n\nELSESUBSTRING(CONVERT(VARCHAR(10),next_run_time),1,2)END),1,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time),3,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time ),5,2)‘Scheduled At’\n\nFROM sysjobs A ,CTE B\n\nWHERE A.job_id = B.job_id\n\nANDSUBSTRING(CONVERT(VARCHAR(10),next_run_date), 5,2)+‘/’+\n\nSUBSTRING(CONVERT(VARCHAR(10),next_run_date), 7,2)+‘/’+\n\nSUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4)=CONVERT(VARCHAR(10),GETDATE(),101)\n\nAND(SUBSTRING(CONVERT(VARCHAR(10),\n\nCASEWHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)> 12\n\nTHENSUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)-12\n\nELSESUBSTRING(CONVERT(VARCHAR(10),next_run_time), 1 ,2)END),1,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time ),3,2)\n\n+‘:’+SUBSTRING(CONVERT(VARCHAR(10), next_run_time ),5,2))>\n\nSUBSTRING(CONVERT(VARCHAR(30),GETDATE(),9),13,7)\n[/code]\n\n

Source: http://blog.sqlauthority.com/2008/12/22/sql-server-find-next-running-time-of-scheduled-job-using-t-sql/

Leave a Reply

Your email address will not be published. Required fields are marked *