|
|
Error Message
Server: Msg 148, Level 15, State 1, Line 2
Incorrect time syntax in time string '<Wait Time>' used
with WAITFOR.
Causes
The WAITFOR statement, one of SQL Server’s Transact-SQL control-of-flow language, blocks the execution of a batch, stored procedure or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.
|
The syntax for the WAITFOR statement is as follows:
WAITFOR { DELAY '<time_to_pass> ' |
TIME '<time_to_execute> ' |
( <receive_statement> ) [, TIMEOUT <timeout> ] }
Specifying DELAY in the WAITFOR statement waits for the specified period of time to pass, up to a maximum of 24 hours, before execution of a batch, stored procedure or transaction proceeds. The period of time to wait is specified in the <time_to_pass> parameter. The <time_to_pass> parameter can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore the date part of the datetime value is not allowed.
Specifying TIME in the WAITFOR statement waits for the specified time when the batch, stored procedure or transaction runs. The time at which the WAITFOR statement finishes is specified in the <time_to_execute> parameter. The <time_to_execute> parameter can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date part of the datetime value is not allowed.
While executing the WAITFOR statement, the transaction is still running and no other requests can be run under the same transaction.
As mentioned earlier, the <time_to_pass> and the <time_to_execute> parameters cannot have the date specified and is not allowed. Specifying the date part in these parameters will generate this error.
Here’s an example on how this error can be encountered using the DELAY option of the WAITFOR statement:
WAITFOR DELAY '2012/05/24 01:00'
PRINT 'Truncating Log File'
BACKUP LOG Northwind WITH TRUNCATE_ONLY
PRINT 'Finished Truncating Log File'
Msg 148, Level 15, State 1, Line 1
Incorrect time syntax in time string '2012/05/24 01:00' used with WAITFOR.
This script tries to schedule the truncating of the log file of the Northwind database one hour from the time the script is executed for the current date (assuming this script is executed on May 24, 2012). But since the date is specified, this error message is generated.
It should be noted that this script will only work for SQL Server 2000 and SQL Server 2005. Trying to execute this script in SQL Server 2008 and later will generate the following error message:
Msg 155, Level 15, State 1, Line 3
'TRUNCATE_ONLY' is not a recognized BACKUP option.
Here’s a similar script that tries to schedule the truncating of the log file of the AdventureWorks database at 9:00PM.
WAITFOR TIME '2012/05/24 21:00'
PRINT 'Truncating Log File'
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
PRINT 'Finished Truncating Log File'
Msg 148, Level 15, State 1, Line 1
Incorrect time syntax in time string '2012/05/24 21:00' used with WAITFOR.
Just like the example earlier, this error is encountered because the date part is specified in the <time_to_execute> parameter.
Solution / Work Around:
To avoid from encountering this error message, when the and parameters are specified as strings or of VARCHAR data type, the date part should not be included.
Using the first example of running the truncation of the log file of the Northwind database one hour after the script is executed, here’s the updated version of the script that will not generate the error:
WAITFOR DELAY '01:00'
PRINT 'Truncating Log File'
BACKUP LOG Northwind WITH TRUNCATE_ONLY
PRINT 'Finished Truncating Log File'
Using the second example doing the same task, here’s how the script will look like that will schedule the log file truncation at 9PM and that will not generate the error:
WAITFOR TIME '21:00'
PRINT 'Truncating Log File'
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
PRINT 'Finished Truncating Log File'
Another way of specifying the <time_to_execute> parameter is with a DATETIME data type variable. Even if the date part is included in a DATETIME data type variable and used in the WAITFOR statement, this error will not be generated as SQL Server will only use the time part. Here’s a sample script that uses a DATETIME data type variable together with the WAITFOR statement:
DECLARE @TimeToExecute DATETIME
SET @TimeToExecute = DATEADD(HH, 1, GETDATE())
WAITFOR TIME @TimeToExecute
PRINT 'Truncating Log File'
BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY
PRINT 'Finished Truncating Log File'
This script will truncate the log file of the AdventureWorks database one hour after the script is initiated.
|