In this blog post, let’s learn about the error message “149 – Time value ‘%.*ls’ used with WAITFOR is not a valid value. Check date/time syntax.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
149 – Time value ‘%.*ls’ used with WAITFOR is not a valid value. Check date/time syntax.
Reason for the Error
Here’s an example that results in the SQL Server error Msg 149:
WAITFOR DELAY 'INVALID_TIME'
The above query attempts to introduce a delay using the WAITFOR statement, but the time value used in the WAITFOR statement is invalid. The time value ‘INVALID_TIME’ is invalid and does not conform to the right date/time syntax. As a result, SQL Server generates error Msg 149.
Solution
To correct this problem, we must use a suitable time value in the WAITFOR statement. The time value provided in the WAITFOR statement should be in the format ‘hh:mi:ss,’ where hh represents hours, mi minutes, and ss seconds. Here’s a modified query that makes use of a correct time value:
WAITFOR DELAY '00:00:30'
In the preceding query, we utilised a valid time value to induce a delay of 30 seconds. This query will return the desired result with no errors.
Alternatively, if we wish to impose a delay in milliseconds, we can use the following syntax:
WAITFOR DELAY '00:00:00:500'
