You must refer to sysschedules first . But as I understand it, there is a typo. it
active_start_time The time on any day between active_start_date and active_end_date that the job starts execution. Time is formatted by HHMMSS using 24-hour mode.
should read like
active_start_time The time any day between active_start_time and active_end_time that the job starts execution. Time is formatted by HHMMSS using 24-hour mode.
And consider this:
active_start_date The start date of the job. The date is formatted as YYYYMMDD. NULL indicates the date today.
This means that if you set up a recurring task to run every day every hour and set active_start_time = 100000 and active_start_date = 20120323, then the work will not work until March 23, 2012 10 AM.
But if you are creating a one-time task, then these fields contain the exact start date and time.
Regarding the search for suitable events, my opinion is as follows. When you update something significant (such as a schedule type or start time) or when a repeating schedule increases the next job, the SQL agent calculates the next run time for the schedule and stores it in sysjobschedules.next_run_date .
Thus, he always has a list of queues the next time and does not solve the problem of “finding the appropriate events for a specific date”. And I think you should implement your system in the same way.
But if you insist on doing it this way, we might think of a T-SQL query for this.
UPDATE
Below is a script that can help you complete your task. Currently it works for two types of schedules:
1. start once
2. start daily every N days, once a day
As far as I understand from your comment, you will use them. You can add additional types as soon as you need them in the same way ( UNION ALL ... UNION ALL ... ).
The msdb_time_convert function converts an integer HHMMSS to a TIME data type.
CREATE FUNCTION msdb_time_convert ( @int_time INT ) RETURNS TIME(0) AS BEGIN IF NOT (@int_time BETWEEN 0 AND 235959) RETURN NULL DECLARE @str VARCHAR(32) = CAST(@int_time AS VARCHAR(32)) SELECT @str = REPLICATE('0', 6 - LEN(@str)) + @str SELECT @str = STUFF(@str, 3, 0, ':') SELECT @str = STUFF(@str, 6, 0, ':') RETURN CONVERT(TIME(0), @str, 108) END GO
You can specify any datetime in the @find_date variable to find the following runs at different points in time.
DECLARE @find_date DATETIME = GETDATE() ;WITH CTE AS ( SELECT CONVERT(DATE, CAST(active_start_date AS VARCHAR(32)), 112) AS active_start_date, CONVERT(DATE, CAST(active_end_date AS VARCHAR(32)), 112) AS active_end_date, dbo.msdb_time_convert(active_start_time) AS active_start_time, dbo.msdb_time_convert(active_end_time) AS active_end_time, schedule_id, schedule_uid, name, enabled, freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, CAST(@find_date AS DATE) AS today_date, CAST(@find_date AS TIME(0)) AS today_time, DATEADD(day, DATEDIFF(day, CONVERT(DATETIME, CAST(active_start_date AS VARCHAR(32)), 112) - 1, @find_date) % NULLIF(freq_interval, 0), CAST(@find_date AS DATE)) AS next_daily_day FROM dbo.sysschedules ) SELECT schedule_id, name, CAST(active_start_date AS DATETIME) + active_start_time AS next_run_datetime FROM CTE WHERE enabled = 1 AND freq_type = 1