November 17, 2009 at 3:03 pm
saying the answer is true is the rough equivalent of saying "it's 5 o'clock somewhere!". 🙂
November 17, 2009 at 3:21 pm
David Easley-401108 (11/17/2009)
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=UTCdeclare @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
So, you are saying that if I run the following code:
select getdate(), getutcdate()
that the values are not equivalent? Why? They both represent the same moment in time (within a few milliseconds of each other).
November 17, 2009 at 3:37 pm
This conversation reminds me of a time when a developer decided that he would change the way an audit table was used. His idea was that using GETUTCDATE() was more "proper". Since this table was a history of transactions this made it incredibly difficult to deal with. Not going into the hair splitting about the equivalance of these dates this caused all sort of problems. When looking through these audit tables to look at transaction times was horrendous. We suddenly had to convert this time to UTC but it became more challenging because you had to take into account which side of daylight saving it was on to know what time it occured "server time". Did this happen at 2pm or 3pm local time? Well that depends...what year was it (since the dates of daylight saving changes yearly). If it was before such and such date it would be -5GMT otherwise it is -6GMT. GOOD GRIEF!!! Needless to say we removed the GETUTCDATE() function as soon as it was discovered. Then we spent several hours resetting the timestamps on thousands of historical transactions. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 17, 2009 at 3:39 pm
Lynn Pettis (11/17/2009)
David Easley-401108 (11/17/2009)
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=UTCdeclare @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
So, you are saying that if I run the following code:
select getdate(), getutcdate()
that the values are not equivalent? Why? They both represent the same moment in time (within a few milliseconds of each other).
To me, they represent the same moment in time but not the same time. Not even equivalent time. But that's me and it's semantics. I think it's a good lesson, though, in how misunderstandings can happen. It's important to be sure that the user's requirements are clear and to question anything that can be taken different ways.
November 17, 2009 at 3:45 pm
If you really consider the practical purpose of getdate() & getutcdate(). One would either use Getdate() or getutcdate(). But not both in the same table for audit purpose. For this very reason both are not equivalent. If they both aer equivalent then they should be interchangable.
Please enlighten me if I am missing something here.
November 17, 2009 at 4:03 pm
Lynn Pettis (11/17/2009)
David Easley-401108 (11/17/2009)
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=UTCdeclare @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
So, you are saying that if I run the following code:
select getdate(), getutcdate()
that the values are not equivalent? Why? They both represent the same moment in time (within a few milliseconds of each other).
they are not equivalent since the results have had any time code related data removed from them. if the output was:
2009-11-16 17:38:41.183 EST(UTC-5)
2009-11-16 17:38:41.183 EST(UTC-5)
2009-11-16 22:38:41.183 UTC
2009-11-16 22:38:41.183 UTC
then I would say that they WERE equivalent.
without the timezone data
2009-11-16 17:38:41.183 <> 2009-11-16 22:38:41.183
even if we don't use any variables and run a straight sql query
SELECT CASE WHEN GETDATE()=GETUTCDATE() THEN 1 ELSE 0 END
the result is that they are NOT equivalent, even with the scope of a single query
November 17, 2009 at 4:18 pm
I have awarded back all points to people as of now. Apologies for the delay, but this was not the high priority of the day.
The question was reworded to use a variable and change "same" to equivalent. I am leaving this as the question as I do think that we are returning equivalent data across time zones, which is the idea. This is not a report, this is T-SQL, and anyone running this query ought to understand that the functions are returning equivalent times across time zones. However you would have to handle the time zone display in your client output (that has been added to the explanation).
You can continue to nitpick, however I believe the intention of the question is good and serves to raise awareness of time zone issues.
November 17, 2009 at 4:33 pm
Steve Jones - Editor (11/17/2009)
I have awarded back all points to people as of now. Apologies for the delay, but this was not the high priority of the day.The question was reworded to use a variable and change "same" to equivalent. I am leaving this as the question as I do think that we are returning equivalent data across time zones, which is the idea. This is not a report, this is T-SQL, and anyone running this query ought to understand that the functions are returning equivalent times across time zones. However you would have to handle the time zone display in your client output (that has been added to the explanation).
You can continue to nitpick, however I believe the intention of the question is good and serves to raise awareness of time zone issues.
Very true, and thanks for the points. Give the critics a small break though. I honestly, or I guess stupidly thought it was a trick question regarding batching commands.
Probably just too much coffee!
November 17, 2009 at 4:51 pm
I understand the issues with the original questions, and the critics made their point. I changed it in a way that I think addresses their issues, but also then continues with the original intent of the question.
November 17, 2009 at 6:23 pm
I realize I'm a bit late to this discussion, but have to vent a bit...
1. Yes, I've submitted QOD's and have suffered the slings and arrows of outraged point-seekers and nitpickers in the associated discussion, not that it's relevant here. Of course, now I'm doubly careful not to confuse "it's" and "its".
2. I got it wrong after reading the question in the daily newsletter and just clicking through and selecting my answer without rereading the now-edited version. I don't know if I would have answered differently to the changed version.
3. Obviously, getdate() and getUTCdate() will often return different values.
4. Almost as obviously, the question was full of obfuscation. I can only guess that the point of that (throwing in date arithmetic and the DateName function) was to act as a red herring.
5. Thinking about it now, I suppose I should have realized that this was a trick question (and I won't put TRICK inside quotes here). Saying that the point of calling 07:00 UCT the same as (or even "equivalent" to) 02:00 EST in this context was to highlight the availablity of the lesser-known function seems a bit disingenuous. It looks more like a bald attempt to elicit "wrong" answers for a topic many participants are actually quite familiar with.
6. I'd like to hear from some more of the 44% of responders who "correctly" answered "TRUE". Did they realize that getUTCdate() returns a different yet "equivalent" value, or what?
7. Lynn, thanks for your thoughtful and intelligent discussion of the underlying concepts. They really are appreciated.
8. I don't care about the points.
Now, back to real life.
--
edit: numbers were out of sequence.... :blush:
November 18, 2009 at 12:34 am
Please give bitbucket and Lynn a break! It is hard to write questions to QoT and I have never and never will write a QoT due to all the preasure from the crowd! I thank you for your currage to write QoT and hope you will continue to do so even if you get crusified when people loose their points as they see it. I also thank you Lynn for your time trying to explain the purpose of the question.
Great job!!!
I love the QoT and Ask SCC. I encurage you all have a look at Ask SCC.
(I removed me from notifications a long time ago, when my mailbox was filling up with angry mails)
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
November 18, 2009 at 12:47 am
The question in the daily newsletter was:
"Will all these statements when executed as a batch return the same date, hour, minute and second?"
Now I see that the question on the webpage is worded differently, to the above the answer should be false, definitely.
November 18, 2009 at 3:06 am
slange-862761 (11/17/2009)
This conversation reminds me of a time when a developer decided that he would change the way an audit table was used. His idea was that using GETUTCDATE() was more "proper". Since this table was a history of transactions this made it incredibly difficult to deal with. Not going into the hair splitting about the equivalance of these dates this caused all sort of problems. When looking through these audit tables to look at transaction times was horrendous. We suddenly had to convert this time to UTC but it became more challenging because you had to take into account which side of daylight saving it was on to know what time it occured "server time". Did this happen at 2pm or 3pm local time? Well that depends...what year was it (since the dates of daylight saving changes yearly). If it was before such and such date it would be -5GMT otherwise it is -6GMT. GOOD GRIEF!!! Needless to say we removed the GETUTCDATE() function as soon as it was discovered. Then we spent several hours resetting the timestamps on thousands of historical transactions. :w00t:
Also, when you fall back, you re-live the same hour more than once. For Example, 1:30 am is a point of time, then when it hits 2:00 am, you rollback the clock to 1:00 am again. Now you have another 1:30 am for the same date. How can you tell if that was the first one or the second one in that day if you have something logged in your database? Using UTC helps with that but like you said, it's not easy to mentally convert that to local time.
November 18, 2009 at 3:09 am
rparakalla (11/17/2009)
If you really consider the practical purpose of getdate() & getutcdate(). One would either use Getdate() or getutcdate(). But not both in the same table for audit purpose. For this very reason both are not equivalent. If they both aer equivalent then they should be interchangable.Please enlighten me if I am missing something here.
I absolutely agree with you on this point. They are not equal and they are not equivalent. That's exactly why there are two different functions and you cannot use them interchangeably.
November 18, 2009 at 3:23 am
Lynn Pettis (11/17/2009)
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.
🙂
Viewing 15 posts - 136 through 150 (of 203 total)
You must be logged in to reply to this topic. Login to reply