SQL Waitfor Delay and SQL Waitfor Time in SQL Server
T-SQL developers use SQL Waitfor statement to postpone or delay the execution of a t-sql script block or to set the execution time of the sql code block.
If a delay in execution of a sql script is wanted, then sql programmers will use waitfor delay command.
If programmers want to set the execution time of a sql code, then they will use SQL Server waitfor time command in their codes.
SQL Waitfor Syntax
Here is the sql WAITFOR statement syntax.
The time period which is used both in Waitfor Delay and Waitfor Time is in HH:MI:SS (hour:minute:second) format.
The time period argument of Waitfor command can not be bigger than 24 hours.
The maximum time parameter value is 23:59:59
WAITFOR DELAY '01:01:01' -- hh:mi:ss
WAITFOR TIME '01:01:01' -- hh:mi:ss
By using the above simple SQL Waitfor command syntax, SQL Server developers can make the SQL engine wait for a while before process waitfor sql code.
SQL Waitfor Delay Command
Here is an SQL WaitFor Delay sample which stops the execution of a sql code for 10 seconds.
After 10 seconds is passed, the sql scripts continues to execute from the last line of sql code where it stopped.
WAITFOR DELAY '00:00:10' -- 10 seconds delay
SQL developers can see the 10 seconds time difference between two SQL Getdate() command outputs easily.
Using the DELAY parameter or DELAY argument, sql programmers can specify the amount of time to wait before execution.
As I have mentioned before, the maximum time delay created using SQL Server Waitfor command is 24 hours (actually 23:59:59).
SQL Waitfor Time Command
If you are a t-sql developer and want your sql code to run exactly at a specific time, then you can use SQL WaitFor Time command.
WAITFOR TIME '22:14:00' -- Run at specific time
SQL developers can easily read the above t-sql code script.
The output of the above sql shows that after the first GETDATE() sql command execution, the second getdate() command waits to run until SQL Server time is 22:14:00 in hh:mi:ss format.
If the sql Waitfor command time parameter value is equal to or bigger than 24:00 (24:00:00) then SQL Server engine will throw the following error.
Msg 148, Level 15, State 1, Line 1
Incorrect time syntax in time string '24' used with WAITFOR.
The only solution for the above error is limiting the time parameter value with maximum 23:59