November 17, 2009 at 1:02 pm
Lynn Pettis (11/17/2009)
Okay, nope, it is called twice. Once for each column. Changed the Tally version to generate 100,000 records. Guess what, Date1 was the same for every row and Date2 was the same for every row but the were different from each other by 0.003 milliseconds.Okay, this gets us to what Jeff always says, test against 1,000,000 rows (even though 100,000 worked here).
So lets recap:
1) Scalar functions are called however many times they are used in a single query.
2) GETDATE() <> GETUTCDATE() (not equal)
3) GETDATE() == GETUTCDATE() (equivalent)
Can we agreement here???
WORKS FOR ME
November 17, 2009 at 1:04 pm
david.wright-948385 (11/17/2009)
Just wondering... did you write the QOD Lynn?
Nope, but I got the answer right without reading the discussion or running the code. I guess the force was strong this morning.
November 17, 2009 at 1:06 pm
cengland0 (11/17/2009)
What about daylight savings time? Getdate() accounts for that because the server time changes. Does the UTC time take into account? I doubt it. So when we are in Eastern time, the difference between 4 or 5 hours.Doesn't the UK also observe daylight savings time and does that affect UTC?
Thankfully, UTC does not change for daylight savings time. If it did, it would make people's lives unduly complicated.
November 17, 2009 at 1:11 pm
Chad Crawford (11/17/2009)
BitBucket and Lynn,I'll throw my hat in the ring in your favor. I can't argue as eloquently as Lynn, but I'll say this - you are absolutely correct. I'm in the Mountain time zone - while it's 9 AM here, it's 4 PM in Greenwich and that is exactly the same time. :w00t: I admit that I didn't think about it that way until Lynn explained it, but it does make perfect sense. Thanks for helping me think outside the box! (wow... there sure are a lot of unhappy people inside that box - come out and join us, we're having a party! :-P)
Chad
Unfortunately I answered False while thinking just the values compared and not the accounting for UTC v. TimeZone. My initial inclination was to answer true, but then I tricked myself out of it and went with false.
That said, I agree with Chad, Lynn and Bitbucket. The final answer of True is correct. As for the seconds issue...that was addressed in the question itself.
"Will all these statements when executed as a batch return an equivalent date, hour, minute and second assuming they all complete within the same second?"
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
November 17, 2009 at 1:16 pm
Was this, assuming they all complete within the same second, always there? I don't even remember seeing it.
November 17, 2009 at 1:17 pm
I agree with all of you, most of us in the planet have to reply False since GETDATE() and GETUTCDATE() don't return the same value, except if you are in the same area where the correctors have tested this code.
We can even guess where they are. 😉
November 17, 2009 at 1:22 pm
christian.ain (11/17/2009)
I agree with all of you, most of us in the planet have to reply False since GETDATE() and GETUTCDATE() don't return the same value, except if you are in the same area where the correctors have tested this code.We can even guess where they are. 😉
I got it correct and I live in the USA, so now what? 😉
You are correct, they don't return the same value, but the values returned represent the same point in time; 8:00 AM MST == 3:00 PM UTC.
November 17, 2009 at 1:25 pm
CirquedeSQLeil (11/17/2009)
"Will all these statements when executed as a batch return an equivalent date, hour, minute and second assuming they all complete within the same second?"
Ah, but you got the redacted version. The original QotD was
Will all these statements when executed as a batch return the same date, hour, minute and second?
The ruckus has been unfair to bitbucket, but it's been interesting.
November 17, 2009 at 1:33 pm
I see that the question has been rewritten as it is now using variables that are set first and changes the word same to equivalent.
Now that the question has been changed to better match the answer, I will agree that anyone who got it wrong prior to the change should be awarded points.
November 17, 2009 at 1:37 pm
Here's something funny: I got the revised version of the question and still got it wrong. My brain equated "equivalent" with "same" and because I'm in the MST zone, didn't think it was correct.
With all the debate on this question, I think this is particularly funny to me. 🙂 I'll blame ... lack of coffee. Yes, that works.
November 17, 2009 at 1:43 pm
I agree with you, but the question is very subtile and can be interpreted with the comparison of the numeric values, and not the corresponding time, specially if you are french like me. Sorry nobody is perfect, I have to improve ...
I understand better like you said it : "equivalent date, hour, minute and second"
November 17, 2009 at 2:07 pm
Lynn Pettis (11/17/2009)
I see that the question has been rewritten as it is now using variables that are set first and changes the word same to equivalent.Now that the question has been changed to better match the answer, I will agree that anyone who got it wrong prior to the change should be awarded points.
I agree with Lynn... the modified query and using 'equivalent' is much better and matches up with the answer explanation.
David
November 17, 2009 at 2:20 pm
Lynn Pettis (11/17/2009)
I see that the question has been rewritten as it is now using variables that are set first and changes the word same to equivalent.Now that the question has been changed to better match the answer, I will agree that anyone who got it wrong prior to the change should be awarded points.
Since we have now changed history (George Orwell -- Nineteen Eighty-Four) I most certainly agree.
November 17, 2009 at 2:58 pm
I agree that the question is now better worded, but still disagree with the answer. Since the output of the queries is devoid of any timezone identification, the dates are NOT equivalent for any timezone not currently=UTC
declare @d DATETIME, @U DATETIME
declare @1 DATETIME,@2 DATETIME,@3 DATETIME, @4 DATETIME
select @d = getdate(), @U = getutcdate()
SELECT @1=DATEADD(day, -1, @d)
SELECT @2=@d - 1
SELECT @3=@u - 1
SELECT @4=DATEADD(day, -1, @U)
SELECTCASE WHEN @1=@2 THEN '1=2' ELSE '1<>2' END
,CASE WHEN @1=@3 THEN '1=3' ELSE '1<>3' END
,CASE WHEN @1=@4 THEN '1=4' ELSE '1<>4' END
,CASE WHEN @2=@3 THEN '2=3' ELSE '2<>3' END
,CASE WHEN @2=@4 THEN '2=4' ELSE '2<>4' END
,CASE WHEN @3=@4 THEN '3=4' ELSE '3<>4' END
GO
Viewing 15 posts - 121 through 135 (of 203 total)
You must be logged in to reply to this topic. Login to reply