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?

Solution 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.

Solution 2

Check this one:

SELECT 

    J.NAME JOB, 

    DATEADD(SS,(H.RUN_TIME)%100,DATEADD(N,(H.RUN_TIME/100)%100,DATEADD(HH,H.RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),H.RUN_DATE),112))))
    JOB_STARTED,  

    DATEADD(SS,((H.RUN_DURATION)%10000)%100,DATEADD(N,((H.RUN_DURATION)%10000)/100,DATEADD(HH,(H.RUN_DURATION)/10000,DATEADD(SS,(H.RUN_TIME)%100,DATEADD(N,(H.RUN_TIME/100)%100,DATEADD(HH,H.RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),H.RUN_DATE),112)))))))
    JOB_COMPLETED,  

    CONVERT(VARCHAR(2),(H.RUN_DURATION)/10000) + ':' + 
    CONVERT(VARCHAR(2),((H.RUN_DURATION)%10000)/100)+ ':' + 
    CONVERT(VARCHAR(2),((H.RUN_DURATION)%10000)%100) RUN_DURATION,

    CASE H.RUN_STATUS 
    WHEN 0 THEN 'FAILED'
    WHEN 1 THEN 'SUCCEEDED'
    WHEN 2 THEN 'RETRY'
    WHEN 3 THEN 'CANCELED'
    WHEN 4 THEN 'IN PROGRESS'
    END RUN_STATUS
    ,CASE S.FREQ_TYPE 
    WHEN  1 THEN 'ONCE'
    WHEN  4 THEN ' DAILY'
    WHEN  8 THEN ' WEEKLY'
    WHEN  16 THEN ' MONTHLY'
    WHEN  32 THEN ' MONTHLY RELATIVE'
    WHEN  64 THEN ' WHEN SQL SERVER'
    ELSE 'N/A' END [FREQ]
    ,CASE 
    WHEN S.NEXT_RUN_DATE > 0 THEN DATEADD(N,(NEXT_RUN_TIME%10000)/100,DATEADD(HH,NEXT_RUN_TIME/10000,CONVERT(DATETIME,CONVERT(VARCHAR(8),NEXT_RUN_DATE),112)))  
    ELSE CONVERT(DATETIME,CONVERT(VARCHAR(8),'19000101'),112) END NEXT_RUN

FROM 

    MSDB..SYSJOBHISTORY H,
    MSDB..SYSJOBS J, 
    MSDB..SYSJOBSCHEDULES S,
    (
    SELECT 
            MAX(INSTANCE_ID) INSTANCE_ID
            ,JOB_ID 
    FROM MSDB..SYSJOBHISTORY GROUP BY JOB_ID
    ) M
WHERE 
    H.JOB_ID = J.JOB_ID 
AND 
    J.JOB_ID = S.JOB_ID 
AND 
    H.JOB_ID = M.JOB_ID 
AND 
    H.INSTANCE_ID = M.INSTANCE_ID

-- IF you want to check all job for today then uncomments below

-- AND 

--     RUN_DATE = (YEAR(GETDATE())*10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())
ORDER BY NEXT_RUN 

Comments

[Marc Gravell]

(please don't double-post; be patient)

[Marc Gravell]

(additional detail excised from duplicate): "I have to manually calculate the value of next_run_date based on freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, etc"

[Francisco]

Yes, but how SQL calculates the value of NEXT_RUN_DATE?

[Francisco]

Do you know the formula? That's the question. Thanks in advance.

[Marcello Miorelli]

I got errors when running this script. s.freq_type?

[Dennis T --Reinstate Monica--]

msdb.dbo.sp_help_jobschedule is much better. sysjobschedules doesn’t get updated immediately after a schedule change, at least in SQL Server 2012 SP4.