SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2016
download SQL Server 2014

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

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems