November 17, 2009 at 8:11 am
Cliff Jones (11/17/2009)
I work with date time functions all of the time and would contend that since the Time Zone is not returned as part of the result, the times are different. My users would expect to see CST and GMT appended to the result in ored for them to discern what time refernce was being used. Without this, I would say the times returned are different.
And this should be handled in the UI, which means that the developers would need to know that the data being provided was either in local or utc.
November 17, 2009 at 8:16 am
bitbucket-25253 (11/17/2009)
Lynn Pettis has explained in great detail the reason the times are the same, and as a matter of fact has explained the reasoning better than I could have. For which I thank Lynn.
No, not the reason that the are the same, but the reason that although they are different, they actually represent the same thing.
My only quibble therefore is that the category is "T-SQL", it should be "English language comprehension" 😉
November 17, 2009 at 8:19 am
This will be false for most of the world. The question didn't state we would all have to move to GMT to answer the question.
That would require a lot of space on someones couch....
November 17, 2009 at 8:23 am
Here is Steve Jones article about the QOD
http://www.sqlservercentral.com/articles/SQLServerCentral/62764/
And quoting from the article here is the objective of the QOD
The Question of the Day is just what it's titled: a daily question about some aspect of databases or SQL Server. It's designed to be a learning mechanism with a bit of fun and competition built into it.
No one is going to learn if they are told the wrong answer. If the reader takes the QOD on face value they will go away with false information, which is essentially what this QOD is.
I've argued the case for QOD contributors' efforts in the past, but this time it plain doesn't wash.
November 17, 2009 at 8:23 am
Lynn Pettis (11/17/2009)
Cliff Jones (11/17/2009)
I work with date time functions all of the time and would contend that since the Time Zone is not returned as part of the result, the times are different. My users would expect to see CST and GMT appended to the result in ored for them to discern what time refernce was being used. Without this, I would say the times returned are different.And this should be handled in the UI, which means that the developers would need to know that the data being provided was either in local or utc.
I see your point. However I can't say that I learned anything new here. But that's ok. Good questions are hard to craft.
November 17, 2009 at 8:24 am
I did not even bother to try and analyze the queries, once I read that the GETDATE() function was executed 4 different times, the only possible answer was false regardless of any timezone or UTC arguments. GETDATE() will potentially return a different value each time the function is executed, yes the difference may only a few milliseconds but it is very possible that those milliseconds can cross seconds. It's easy to create a looping function to test this and you will come up with different seconds.
To be accurate, the question should have loaded GETDATE into variable and use that instead of re-executing the function.
David
November 17, 2009 at 8:34 am
David_Simpson (11/17/2009)
I did not even bother to try and analyze the queries, once I read that the GETDATE() function was executed 4 different times, the only possible answer was false regardless of any timezone or UTC arguments. GETDATE() will potentially return a different value each time the function is executed, yes the difference may only a few milliseconds but it is very possible that those milliseconds can cross seconds. It's easy to create a looping function to test this and you will come up with different seconds.To be accurate, the question should have loaded GETDATE into variable and use that instead of re-executing the function.
David
I agree!
November 17, 2009 at 8:41 am
Add me to the growing contingent who protests the answer.
I got . . .
2009-11-16 10:37:19.917
2009-11-16 10:37:19.930
2009-11-16 15:37:19.930
2009-11-16 15:37:19.930
Looks like it's FALSE to me!!!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 17, 2009 at 8:48 am
Ray K (11/17/2009)
Add me to the growing contingent who protests the answer.I got . . .
2009-11-16 10:37:19.917
2009-11-16 10:37:19.930
2009-11-16 15:37:19.930
2009-11-16 15:37:19.930
Looks like it's FALSE to me!!!
The question only goes down to seconds accuracy... the 917 vs 930 is miliseconds.
However, look back and you'll find my little looping function that runs the batch over and over until it hits a combination where the seconds are actually different.
November 17, 2009 at 8:51 am
Okay, I give up. No one is really paying attention to the concept that the question was trying to bring out. Everyone is concentrating on the actual values returned.
Yes, getdate() and getutcdate() return different values, just look at what they return when you run them. However both functions return values that are equivelent to each other, 8:00 AM MST == 9:00 AM CST == 10:00 AM EST == 3:00 PM UTC.
November 17, 2009 at 8:52 am
Lynn Pettis (11/17/2009)
7:00 AM <> 2:00 PM, true7:00 AM MST = 2:00 PM UTC, true.
That is the concept that the question is trying to address.
Yes, but there are two flaws with the question.
1. See my looping batch query that eventually hits a combination where the seconds don't match (the question specified down to the second).
2. The question is asking us to compare the data and the data does not contain the timezone in it, so I'm comparing 14:00:00 to 20:00:00 with a tmie zone difference of -6 and with nothing in the data to tell me what time zone it came from, I can only conclude the data when comparing date, hours, minutes, and seconds is different.
November 17, 2009 at 8:54 am
As mentioned by Rich B, you can cross the seconds boundary. It depends when you execute.
2009-11-16 10:48:29.997Monday
2009-11-16 10:48:29.997Monday
2009-11-16 15:48:30.000Monday
2009-11-16 15:48:30.000Monday
November 17, 2009 at 8:55 am
Lynn Pettis (11/17/2009)
Okay, I give up. No one is really paying attention to the concept that the question was trying to bring out. Everyone is concentrating on the actual values returned.
Because that's what the question is telling us to do.
Compare the date, hour, minute, and seconds returned.
Will all these statements when executed as a batch return the same date, hour, minute and second?
And the answer is NO. They are equivalent, but they are not the SAME.
November 17, 2009 at 8:55 am
You on the same longitude as Greenwich England, Bitbucket?
This is the first time I can think of that I've been discriminated against based on my geography.
While the values may be equivalent, the word SAME and calling out hours minutes and seconds leads one to think of the values returned rather than equivalence.
November 17, 2009 at 8:57 am
Lynn Pettis (11/17/2009)
Okay, I give up. No one is really paying attention to the concept that the question was trying to bring out. Everyone is concentrating on the actual values returned.Yes, getdate() and getutcdate() return different values, just look at what they return when you run them. However both functions return values that are equivelent to each other, 8:00 AM MST == 9:00 AM CST == 10:00 AM EST == 3:00 PM UTC.
I agree with you in that "8:00 AM MST == 9:00 AM CST == 10:00 AM EST == 3:00 PM UTC" but none of that matters because the GETDATE() function is executed four different times. You can throw the UTC queries out if you want and the answer is still false because the GETDATE() function can return different milliseconds which can cross seconds.
Conceptually, though, you are correct.
David
Viewing 15 posts - 46 through 60 (of 203 total)
You must be logged in to reply to this topic. Login to reply