Always Developing - Delay SQL execution with WAITFOR

Daily Knowledge Drop

SQL is a WAITFOR command which an be used to delay the execution of a proceeding SQL statement. One of two options can be supplied to the WAITFOR statement:

TIME: waits until the time of day specified before executing the next statement
DELAY: waits for the time span specified before executing the next statement

WAITFOR Format

The format and usage of WAITFOR is straightforward:

WAITFOR DELAY 'time_to_pass' | TIME 'time_to_execute'

WAITFOR DELAY

WAITFOR DELAY will wait the specified time span before executing the next command:

PRINT CONVERT(varchar, SYSDATETIME(), 121)
GO

WAITFOR DELAY '00:00:05';
GO

PRINT CONVERT(varchar, SYSDATETIME(), 121)
GO

Executing the above will result in the following output:

2022-09-08 20:39:51.2166773
2022-09-08 20:39:56.2387040

Completion time: 2022-09-08T20:39:56.2397037+02:00

A time span between 00:00:00.001 and 23:59:59.998 can be specified - anything longer will result in an error.

WAITFOR TIME

WAITFOR TIME will wait until the specified time of day before executing the next command:

PRINT CONVERT(varchar, SYSDATETIME(), 121)
GO

WAITFOR TIME '20:47:00';
GO

PRINT CONVERT(varchar, SYSDATETIME(), 121)
GO

Executing the above will result in the following output:

2022-08-15 20:46:48.9212859
2022-08-15 20:47:00.0130443

Completion time: 2022-08-15T20:47:00.0140433+02:00

In this example, we only had to wait 12 seconds, but the wait could be longer depending on the time specified. Only a time can be specified, not a particular date. If the next execution of the specified time is the following day, the wait until the time is reached the following day.

Scheduling

The below is an example of how the functionality can be used to executing a command on a specific schedule. This technique is used for demo purposes not necessarily recommended for a production use case.

The following will execute the command every second for a full minute, once a day:

-- wait until a specific time
WAITFOR TIME '20:52:00';
GO

-- while it is the 53th minute
WHILE(DATEPART(MINUTE, GETDATE()) < 54)
BEGIN
        -- print out the date time
	BEGIN
		PRINT CONVERT(varchar, SYSDATETIME(), 121)
	END

        -- wait for 1 second
	BEGIN
		WAITFOR DELAY '00:00:01';
	END
END
GO

The output:

2022-08-15 20:52:00.0145585
2022-08-15 20:52:01.0162047
2022-08-15 20:52:02.0209920
2022-08-15 20:52:03.0229534
.
.
.
2022-08-15 20:53:56.6402032
2022-08-15 20:53:57.6424606
2022-08-15 20:53:58.6450557
2022-08-15 20:53:59.6499793

Notes

While not necessarily a command which will see every day usage, it could prove useful to simulate a specific "real world" scenario when there is a time span between statements, either for testing or investigation purposes.

References

SQL WAITFOR Command to Delay SQL Code Execution

Daily Drop 157: 09-09-2022

At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.

The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.

sql sqlserver waitfor delay

Delay SQL execution with WAITFOR

Using the SQL WAITFOR command to delay the execution of a SQL statement