I had a kind of unusual requirement the other day. I needed to pause a SQL Agent job, and have it wait for a specific time of day before it could proceed. The basic idea was that the job had several longer running components, and needed to start earlier in the day. But, there was a point where it had to wait for a non SQL Event to happen on a non SQL Server before it could proceed. That other system had some services disabled until 7:30 AM. After my job had run its course, and waited until after 7:30 it would execute a process and send an event trigger to the other system. We had to wait for the other system to be online before we executed that trigger event from the SQL Agent job.
Wait for a specific time of day in a SQL Agent job step
The requirement is odd, but the code is pretty simple. Add a step to your job between the step that can complete without the time constraint, and the step that needs to wait for the specific time of day. Then in the code window, put the code below, and adjust your time as necessary in the @MinutesSinceMidnight value.
DECLARE @Today DATETIME2(0) = CONVERT(DATE,GETDATE()) --convert to Date to strip off the Time ,@MinutesSinceMidnight SMALLINT = DATEDIFF(mi,0,'07:32:00') ,@Go TINYINT = 0; WHILE @Go = 0 BEGIN SELECT @Go = CASE WHEN DATEADD(mi,@MinutesSinceMidnight,@Today) > GETDATE() THEN 0 ELSE 1 END; IF @Go = 0 BEGIN WAITFOR DELAY '00:01'; END; END;
We start by declaring a few variables. One for the current date with a time value of 00:00:00.. Another for the number of minutes since midnight, and then one more to track whether or not the trigger time has passed.
A bit of explanation for the @MinutesSinceMidnight value. 7:32 AM is the time I decided to use as my trigger time. So I find the difference in minutes, between midnight and 7:32 AM. When dealing with time, zero functionally represents midnight. As I am sure you have guessed by now, the variable is named for the actual value it holds.
Then comes the loop construct. Basically each iteration of the loop will check to see if the test time has passed. If it has passed, then @Go is set to 1, and the WHILE stops. If the current time is still earlier than 7:32 AM the process will wait for 1 minute, and then loop again. The loop will continue until the reference date time is greater than the current date time; when that happens the code will drop out of the loop and the command will complete. When the command completes, the SQL Agent job step completes and the next step proceeds.
You can change the trigger time by updating the value used in the @MinutesSinceMidnight declaration. You can also get the loop to wait for more or less time by changing the WAITFOR value used.
I hope you find this information useful. Thanks for reading.