SQL Server Error – Incorrect time syntax in time string used with WAITFOR.

This is a simple one but it doesn’t stick until you write/read about it.  Note, I use WAITFOR DELAY to pause/sleep the session between batches or T-SQL commands. For logic reasons, sometimes, I just gotta buy myself a few seconds for one thing to finish, before I start the next one.

Ref: Control-of-Flow (lots of goodies there)

WAITFOR DELAY '00:00:60'

Msg 148, Level 15, State 1, Line 1
Incorrect time syntax in time string '00:00:60' used with WAITFOR.

(Fixed) Used instead.

WAITFOR DELAY '00:00:59'

The syntax error occurs because the value passed in the string is an invalid time (out of range). I was trying to specify 60 seconds which there’s really no such thing; it’s actually ’00:01:00′ therefore ’00:00:60′ is invalid.

The valid ranges are:
Hour: 00-23
Minute: 00-59
Second: 00-59
Milliseconds: 000-999

ie. Max at ’23:59:59.999′. /*Don’t wait that long.*/

 

Sounds good to me. Onto the next one. 🙂

Hiram

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s