How SQL calculates NEXT_RUN_DATE for a job schedule?

Question

I have to make a manual calculation of the next run date for a job, can you help me?

Answer 1

to get the next run date for a job you can use then sysschedules and sysjobschedules tables

check the next_run_date and next_runtime columns from the table sysjobschedules

next_run_date int Next date on which the job is scheduled to run. The date is formatted YYYYMMDD.

next_run_time int Time at which the job is scheduled to run. The time is formatted HHMMSS, and uses a 24-hour clock.

see this script

Select sched.*,jobsched.* FROM msdb.dbo.sysschedules AS sched
inner Join msdb.dbo.sysjobschedules AS jobsched ON sched.schedule_id = jobsched.schedule_id

or you can use the msdb.dbo.sp_help_jobschedule stored procedure, to get the same info.

UPDATE

if you need calculate manually the next_run_date you must check the sysschedules table and see the freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, active_start_date, active_start_time columns to determine the formula.

check this link to see an example of use.

Answer 2

The short answer is that there's no single formula - it varies by value of freq_type.

The site seems to be down at the moment of writing, but there is an article at http://www.sqlmag.com/Article/ArticleID/99593/sql_server_99593.html which covers how to derive this information. Unfortunately, the site doesn't allow Google to cache its content, so it can't be retrieved until the site comes back up

This looks like a decent alternative source for the kind of query you're trying to write.