Sql Server Error: Incorrect time syntax in time string used with WAITFOR

ERROR SCENARIO

We can get this exception if we try to execute statement like below to to SLEEP or WAIT for 90 seconds

WAITFOR DELAY '00:00:90.000'

RESULT:

Msg 148, Level 15, State 1, Line 1
Incorrect time syntax in time string ’00:00:90.000′ used with WAITFOR.

WHY THIS EXCEPTION?

The parameter which is passed to the WAITFOR DELAY control flow statement should be valid time. In this case the second part specified is 90 which is invalid, the valid value for the second part is between 0 and 59. Any value other than this will raise any exception. Similarly, valid value for the time part is from 0 to 59, valid value for the hour part is from 0 to 23 and for milliseconds the valid value is from 000 to 999.

WHAT IS THE SOLUTION?

To resolve this issue, we can convert 90 seconds as 1 Minute and 30 Seconds and pass it as parameter to the WAITFOR DELAY statement as shown in the below example:

WAITFOR DELAY '00:01:30.000'

RESULT:
wait-for-1-minute-and-30-seconds-in-sql-server

Leave a Reply

Your email address will not be published.