January 10, 2003 at 11:56 am
Is it guaranteed that 2 GETDATE() calls in the same SELECT statement are always equal? For Example are A and B always going to be equal?:
SELECT A=GETDATE(), B=GETDATE() WHERE GETDATE()=GETDATE()
-Dan
-Dan
January 10, 2003 at 12:14 pm
Dont know. I wouldnt think so. Figure its easier to evalulate the function each time than to keep track of whether you've already evaluated. If done each time, they could be different.
Andy
January 10, 2003 at 12:30 pm
I think that in most cases it would be equal. But I believe it would be determined by how long the query takes (ie. if the second getdate() occurs more than a second after the first getdate() then I believe the two times would be different).
-SQLBill
January 10, 2003 at 12:37 pm
I just ran a script that copied 300,000+ rows into a temp database, got the getdate(), counted all the rows (count(*)), then got the getdate(). The query took several seconds, but both times were exactly the same. Suprised me, I expected the first getdate() to grab the system time at the moment that function ran, then to grab the system time again when it ran the second time.
-SQLBill
January 10, 2003 at 12:37 pm
I would think they would be equal since the getdate functions should be evaluated during the "compiling" of the query. But to be sure, I would create a variable to hold the getdate value and use the variable everywhere in the query (you know what they say about assume!)
Jay
Jay Madren
January 10, 2003 at 2:08 pm
I did a little testing, and this is what I got:
It looks like all GETDATE() calls in a single statement are evaluated at the beginning of the statement (parse time)?
select getdate()
waitfor delay '000:00:01'
select count(*),getdate() from DB1..Large_Table
select getdate()
------------------------------------------------------
2003-01-10 14:58:15.577
(1 row(s) affected)
----------- ------------------------------------------------------
17648828 2003-01-10 14:58:16.577
(1 row(s) affected)
------------------------------------------------------
2003-01-10 14:58:22.077
(1 row(s) affected)
-Dan
-Dan
January 10, 2003 at 8:45 pm
Intersting, have to check on this. Will try to ping some MS contacts to see if we can get an explanation of the coding.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 11, 2003 at 8:47 am
Just to be stuck to the wording I would say it is not garanteed but most probably it will be the same.
But as Dan has shown us it couls be a kind of MS technics to evaluate the getdate() at the parse time, but I wouldn't trust it will remain within the following SP's and versions
Bye
Gabor
January 13, 2003 at 10:47 am
Multiple GETDATE()s in one SQL statement will evaluate to a single value. Thus,
UPDATE Table
SET Column = GETDATE()
will set the Column column to the same date value regardless of how big the table is and how long the query takes.
However, GETDATE()s in different SQL statements will probably evaluate to different values. If you need to use a single date value in multiple SQL statements, its better to store them in a variable that you can reuse.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply