Waiting for a delay

Question

Waiting for a delay

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
April 30, 2016 at 1:29 pm

#77478

Comments posted to this topic are about the item Waiting for a delay
May 2, 2016 at 11:53 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

A nice and simple way to start the week. Then again, that's one of those command I didn't have to look up. Thanks, Steve.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

webrunner SSC-Dedicated Points: 31863 More actions · Answer 2

What a kooky aspect of this statement, but thanks for the question!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Revenant SSC-Forever Points: 42467 More actions · Answer 3

Revenant

SSC-Forever

Points: 42467

May 2, 2016 at 9:35 am

#1875929

Simple, straightforward - thanks, Steve!

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Nice tidy question.

Since the time can be specified as a datetime variable whose date part is 1901-01-01 or as a string variable representing a datetime with the year part missing, it's hard to understand why a time(3) or less precise time (or datetime2) variable shouldn't simply be implicitly converted to datetime or to varchar(23) or nvarchar(23). The reason is that WAITFOR is some bizarre sort of object, not a function, and the 'time to wait' bit of it isn't an expression that delivers a datetime value with 1901-01-01 date but must be one of (i) a datetime variable with 1901-01-01 year value or a string value that specifies hours, minutes, seconds and milliseconds in a form implicitly covertible to datetime. I thought it a bit sad that WAITFOR wasn't upgraded to cope with the time and datetime2 formats when they were first added to SQL Server.

Tom

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Thanks for another lesson involving dates and times.

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 6

I'm surprised that in the BOL is stated: "Dates cannot be specified; therefore, the date part of the datetime value is not allowed." But the following script can be used without error:

DECLARE @t DATETIME = '2016-05-02 00:00:1.777';

WAITFOR DELAY @t;

SELECT @t as [Local variable @t];

/* Results

Local variable @t

-----------------------

2016-05-02 00:00:01.777

(1 row(s) affected)

*/

TomThomson SSC Guru Points: 104773 More actions · Answer 7

George Vobr (5/2/2016)
I'm surprised that in the BOL is stated: "Dates cannot be specified; therefore, the date part of the datetime value is not allowed." But the following script can be used without error:
DECLARE @t DATETIME = '2016-05-02 00:00:1.777';
WAITFOR DELAY @t;
SELECT @t as [Local variable @t];
/* Results
Local variable @t
-----------------------
2016-05-02 00:00:01.777
(1 row(s) affected)
*/

Interesting. I made the mistake of believing BOL and didn't bother to check that dates other than 1901-01-01 wouldn't work. Careless of me :blush:. But not an error that will make things not work, fortunately 😎

Tom

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Interesting question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

webrunner (5/2/2016)
What a kooky aspect of this statement, but thanks for the question!
- webrunner

+10

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events