T-SQL

Question

T-SQL

Viewing 15 posts - 31 through 45 (of 203 total)

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

nigel. SSChampion Points: 11636 More actions · Answer 1

Yeah, like what everyone else says - the question is misleading and both answers are actually right in different time zones. Can I have my points now please 😉

Nigel
How to post questions

Steve Eckhart SSCommitted Points: 1880 More actions · Answer 2

If the SELECT statements had been formulated to indicate the time zone (e.g., 2009-11-16 08:01:18.177 CST, 2009-11-16 14:01:18.177 GMT), then what you are saying would be correct. A person viewing the result sets could conclude they were the same time. However, the actual result set doesn't include any indication of the time zone, so a reasonable person would conclude that the results were two different times.

Sorry, the answer was just plain wrong leaving aside the fact that the question was phrased as a yes/no question and not as a true/false question.

Maybe the author meant that false meant "yes" and true meant "no".

Steve Eckhart

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

cengland0 (11/17/2009)
Lynn, you have a valid point but if you were to show those times to a client, they would only see the output not the representation. So, for example, if someone wanted a report pulled that showed "clock in times" and I showed that time in UTC, they would freak out thinking the report is wrong and I would have to agree with them.

In a report, if I were to be displaying the date/time in UTC, I would be sure that the heading clearly stated that it was UTC. That comes down to presentation.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

crussell-931424 (11/17/2009)
I got the following data:
2009-11-16 07:05:34.010 Monday
2009-11-16 07:05:34.010 Monday
2009-11-16 14:05:34.017 Monday
2009-11-16 14:05:34.017 Monday
The seconds were different. So the answer is false. I want my score corrected.

Actually, the seconds are the same, the difference is in the milliseconds.

mtassin SSC-Insane Points: 23099 More actions · Answer 5

Lynn Pettis (11/17/2009)
david.wright-948385 (11/17/2009)
I think getdate() = getutcdate() would return false in your time zone, so I think they're probably different!
Everyone is looking at the values, yes, they are "different", but both represent the same time. One is local, the other UTC.

This may be true Lynn but,

There is nothing inherent in the Datetime data type that references what time zone the data was generated in.

If I use datepart on the two values to get the hour, I get 8 and 14. Which are not the same Hour.

If I calculate DATEDIFF(hh,getdate(),getutcdate()) I'll get a value other than 0 as well.

Saying that they are the same because I can interpret one by looking at the code to be a UTC adjusted datetime does not make the two datetime results equivalent.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

amarshall-568002 Right there with Babe Points: 785 More actions · Answer 6

amarshall-568002

Right there with Babe

Points: 785

November 17, 2009 at 7:36 am

#1080244

I answered false. I get different results.

david.wright-948385 SSCarpal Tunnel Points: 4041 More actions · Answer 7

Lynn Pettis (11/17/2009)
david.wright-948385 (11/17/2009)
I think getdate() = getutcdate() would return false in your time zone, so I think they're probably different!
Everyone is looking at the values, yes, they are "different", but both represent the same time. One is local, the other UTC.

...so they're not the same 🙂 Stop digging!!

mtassin SSC-Insane Points: 23099 More actions · Answer 8

Ooh and I managed to get the query to return different seconds as well

(No column name)(No column name)

2009-11-16 08:45:49.000Monday

2009-11-16 14:45:48.997Monday

Although I'm a bit confused exactlyhow the later version that picks up the UTC datetime is earlier in seconds than the local datetime.

DECLARE @date1 datetime

DECLARE @date2 datetime

DECLARE @date3 datetime

DECLARE @date4 datetime

WHILE @date1 IS NULL OR (datepart(ss,@date1)-datepart(ss,@date4) = 0)

BEGIN

SELECT @date1=DATEADD(day, -1, GETDATE())

SELECT @date2=GETDATE() - 1

SELECT @date3=GETUTCDATE() - 1

SELECT @date4=DATEADD(day, -1, GETUTCDATE())

END

SELECT @date1,datename(dw,@date1)

SELECT @date2,datename(dw,@date2)

SELECT @date3,datename(dw,@date3)

SELECT @date4,datename(dw,@date4)

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

7:00 AM <> 2:00 PM, true

7:00 AM MST = 2:00 PM UTC, true.

That is the concept that the question is trying to address.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

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.

Hopefully all who have been taking issue with what is, or is not the correct answer will have had their knowledge of date time functions expanded and will remember this.

Another point: To all who are taking issue with the questions wording. I challenge you to compose and submit a question of the day. Remembering that the QOD is meant not only to test knowledge of T-SQL, but to also teach.

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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

cengland0 SSCertifiable Points: 6102 More actions · Answer 11

crussell-931424 (11/17/2009)
I got the following data:
2009-11-16 07:05:34.010 Monday
2009-11-16 07:05:34.010 Monday
2009-11-16 14:05:34.017 Monday
2009-11-16 14:05:34.017 Monday
The seconds were different. So the answer is false. I want my score corrected.

Did you run that in a batch?

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

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.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 13

Cliff Jones
My users would expect to see CST and GMT appended to the result

What you are discussing has been addressed by Lynn Pettis, you are referencing the mode of presentation, which is not what the question asked.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Mauve SSChampion Points: 11316 More actions · Answer 14

Lynn Pettis (11/17/2009)
7:00 AM <> 2:00 PM, true
7:00 AM MST = 2:00 PM UTC, true.
That is the concept that the question is trying to address.

Maybe, but we are not "mind readers".

The question asked "Will all these statements when executed as a batch return the same date, hour, minute and second?"

Note the word "return". They do not return the same values.

This question is akin to the vague specifications that application designers/architects get from marketing, users, etc.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Cliff Jones SSChampion Points: 10547 More actions · Answer 15

As a practicle matter, I would never win that argument with my clients.