SQL Server 2008, WAITFOR DELAY/TIME

The WAITFOR Statement

There are often things that you either don’t want to or simply can’t have happen right this moment, but

you also don’t want to have to hang around waiting for the right time to execute something.

No problem — use the WAITFOR statement and have SQL Server wait for you. The syntax is incredibly

simple:

WAITFOR

DELAY <’time’> | TIME <’time’>

The WAITFOR statement does exactly what it says it does. It waits for whatever you specify as the argument to occur. You can specify either an explicit time of day for something to happen, or you can specify an amount of time to wait before doing something.

The DELAY Parameter

The DELAY parameter choice specifies an amount of time to wait. You cannot specify a number of

days — just time in hours, minutes, and seconds. The maximum allowed delay is 24 hours. So, for

example:

WAITFOR DELAY ‘01:00’

would run any code prior to the WAITFOR, then reach the WAITFOR statement, and stop for one hour, after which execution of the code would continue with whatever the next statement was.

The TIME Parameter

The TIME parameter choice specifies to wait until a specific time of day. Again, we cannot specify any

kind of date — just the time of day using a 24-hour clock. Once more, this gives us a one-day time limit

for the maximum amount of delay. For example:

WAITFOR TIME ‘01:00’

would run any code prior to the WAITFOR, then reach the WAITFOR statement, and stop until 1 AM, after which execution of the code would continue with whatever the next statement was after the WAITFOR.