T-SQL

Question

Post reply

T-SQL

Viewing 15 posts - 76 through 90 (of 203 total)

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

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 1

There, fixed it...

SELECT DATEADD(day, -1, GETDATE()),+ ' Local time',DATENAME (dw ,DATEADD(day, -1, GETDATE()) )

SELECT GETDATE() - 1,+ ' Local time', DATENAME (dw ,GETDATE() - 1)

SELECT GETUTCDATE() - 1,+ ' UTC', DATENAME (dw,GETUTCDATE() - 1)

SELECT DATEADD(day, -1, GETUTCDATE()),+ ' UTC', DATENAME (dw,DATEADD(day, -1, GETUTCDATE()))

GO

Although I still would say "equivalent", not the "same".

Tom_Sacramento SSCertifiable Points: 6296 More actions · Answer 2

I don't get the same hour, but the same date, minute, and seconds

The hour could not match since my local time is not GMT, (GMT+8).

My results:

2009-11-16 09:02:17.020 Monday

2009-11-16 17:02:17.013 Monday

How is this the 'same'?

puzzled

Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

cengland0 SSCertifiable Points: 6102 More actions · Answer 3

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

Are you sure about that? Even if you run it in a batch?

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

Toreador (11/17/2009)
Lynn Pettis (11/17/2009)
You know, I haven't tried writing a QotD. Have you?
Ah, that old chestnut.
I've never been Prime Minister either, but I still get to say what I think about our current one 🙂

The idea here is to put yourself in their shoes. What were they thinking, or more to the point, what concept are they actually trying to get across. The question may not be written in the best, most effective way, but don't look at it from a straight black & white perspective.

rparakalla Right there with Babe Points: 745 More actions · Answer 5

Most of the questions posted in QOD is to answer the question and not the intentions of the person who writes the question I guess

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

cengland0 (11/17/2009)
Are you sure about that? Even if you run it in a batch?

Even in a batch... the odds are about 50/50 that I'll get different results for the miliseconds.. the odds shrink quite a bit for differing seconds.

Union Results

(No column name)(No column name)

2009-11-16 11:07:06.153Monday

2009-11-16 17:07:06.157Monday

Run the results in a transaction

(No column name)(No column name)

2009-11-16 11:07:06.153Monday

2009-11-16 17:07:06.157Monday

Run the results as a batch (between GO statements)

(No column name)(No column name)

2009-11-16 11:07:06.247Monday

2009-11-16 17:07:06.250Monday

--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]

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 7

The question was so clear, you actually could not misinterpret it:

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

Or to put it a bit more specific:

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

We have seen so many examples that we may easily state: except for a minority of cases the answer to the above question must be negative. So far, so easy.

Bitbucket's point that he/she wanted an answer about the time itself and not about its presentation is lame and makes the question even worse, as the subject was what the statements return, not what the system clock returns. It delivers an image of somebody sitting in a dark corner at a cluttered desk, rubbing hands with glee, and mumbling "Ha ha, missed your points, ha ha, missed your points..."

And finally trying to argue that the whole thing were for educational purposes is even more cynical. I hope nobody ever had or will have a teacher trying to teach that way. Guiding students purposefully into the wrong direction may even be disastrous.

I cannot remember a worse QOD than this one.

Best regards,
Dietmar Weickert.

cengland0 SSCertifiable Points: 6102 More actions · Answer 8

[Quote]Even in a batch... the odds are about 50/50 that I'll get different results for the miliseconds.. the odds shrink quite a bit for differing seconds.

Union Results

(No column name) (No column name)

2009-11-16 11:07:06.153 Monday

2009-11-16 17:07:06.157 Monday

Run the results in a transaction

(No column name) (No column name)

2009-11-16 11:07:06.153 Monday

2009-11-16 17:07:06.157 Monday

Run the results as a batch (between GO statements)

(No column name) (No column name)

2009-11-16 11:07:06.247 Monday

2009-11-16 17:07:06.250 Monday

[/Quote]

That's excellent information. Thanks. I had always thought that if you ran getdate() multiple times in a batch that it would be the same value. So I did actually learn something from this QOTD after-all.

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 9

cengland0 (11/17/2009)
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
Are you sure about that? Even if you run it in a batch?

GETDATE() is an nondeterministic function so that every time this function is executed the results are not guaranteed to be the same, regardless of how the function is executed.

The answer should be false because if for no other reason then how the queries were written will return different seconds regardless of timezones.

None-the-less, I think QOD spawned off some really good discussions. Thanks for a good morning Bitbucket.

David

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

rparakalla (11/17/2009)
Most of the questions posted in QOD is to answer the question and not the intentions of the person who writes the question I guess

I disagree. To answer a question is more than reading the words and selecting an answer. You have to understand what the question is asking. Most of the QOD that generate such heat as this one were read very literally instead of looking at WHAT was being asked, not HOW it was being asked.

Dietmar Weickert (11/17/2009)
The question was so clear, you actually could not misinterpret it:
Will all these statements when executed as a batch return the same date, hour, minute and second?
Or to put it a bit more specific:
Will all these statements when executed as a batch return the same date, and the same hour, and the same minute, and the same second?
We have seen so many examples that we may easily state: except for a minority of cases the answer to the above question must be negative. So far, so easy.
Bitbucket's point that he/she wanted an answer about the time itself and not about its presentation is lame and makes the question even worse, as the subject was what the statements return, not what the system clock returns. It delivers an image of somebody sitting in a dark corner at a cluttered desk, rubbing hands with glee, and mumbling "Ha ha, missed your points, ha ha, missed your points..."
And finally trying to argue that the whole thing were for educational purposes is even more cynical. I hope nobody ever had or will have a teacher trying to teach that way. Guiding students purposefully into the wrong direction may even be disastrous.
I cannot remember a worse QOD than this one.

Actually, I remember several questions that you could consider just as "bad" if you want, I just don't feel like looking for them.

And if you take exception to that this is for educational purposes, then what is the QotD for in your opinion. It is my understanding that is exactly what QotD was meant to accomplish.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 11

Dietmar Weickert (11/17/2009)
I cannot remember a worse QOD than this one.

This is true, this question seems to do the trick of becoming a clear leader in this category. In SQL Server, the smallest tick is 3 milliseconds, and there are 3 possible values of the rightmost digit of the time part, namely 0, 3, and 7. In other words, it is not possible to for example use the dateAdd function to add a millisecond to any given datetime value (such the attempt will be simply ignored by the engine). While Lynn Pettis provided a very clear explanation of what the question actually meant, it is very easy to run the QoD statements just before the second ends (at .997) to see the second flip, 59.997 to see the minute flip etc. There are 4 statements in the batch and neither of them uses a cached datetime value but rather queries getDate and getUtcDate live. The batch does not have to use the whole 3 milliseconds to run, just long enough to flip the .997 to the next possible value, which is .000 of the next second. This could be a fraction of a millisecond and still it is possible to get second, minute, hour, day, and even year numbers to flip depending on when does the query get executed. This completely negates the true as a correct answer even for people living within GMT and even considering Lynn's explanation. At best the true part should be changed to true most of the time and false otherwise.

Oleg

Jared Meaux SSC Enthusiast Points: 167 More actions · Answer 12

I agree, the correct anwser is false. When I run these commands I get:

2009-11-16 12:08:12.923 Monday

2009-11-16 18:08:12.937 Monday

I want my point back 🙂

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 13

Lynn Pettis (11/17/2009)
I disagree. To answer a question is more than reading the words and selecting an answer. You have to understand what the question is asking. Most of the QOD that generate such heat as this one were read very literally instead of looking at WHAT was being asked, not HOW it was being asked.

We are not to look at WHAT was being asked but HOW it was being asked? What else but the written words do you rely on? Body language of the author? Face expression?

Lynn Pettis (11/17/2009)
And if you take exception to that this is for educational purposes, then what is the QotD for in your opinion. It is my understanding that is exactly what QotD was meant to accomplish.

To put it your way: seems you perceived only WHAT was written, not HOW it was written. Of course the QOD should be and usually is for educational purposes. And many of them are excellent. Just this particular one is bad. Hence justifying the bad question with educational intentions is cynical.

Best regards,
Dietmar Weickert.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 14

Well technically its NOT TRUE, since the real answer is SOMETIMES. That isn't an option, so FALSE would be the logical choice.

This question is kind confusing because of the wording, but the point is well taken.

Because executing as a batch has nothing to do with when the internal clock is accessed. The statements execute sequentially. You can easily see where the milliseconds are sometimes different. So the seconds component must be different sometimes.

But Lynn's first comment put them in the same SELECT statement, and that got me wondering. I suspected it still had to be sequential for each column, but here's the proof.

CREATE TABLE #mytemp( A datetime, B datetime )

GO

SET NOCOUNT ON

INSERT INTO #mytemp

SELECT

GETDATE(), GETDATE()

GO 100000

SELECT * FROM #mytemp WHERE A <> B

DROP TABLE #mytemp

Yielded:

Beginning execution loop

Batch execution completed 100000 times.

A B

----------------------- -----------------------

2009-11-16 13:05:13.457 2009-11-16 13:05:13.470

2009-11-16 13:05:14.220 2009-11-16 13:05:14.237

2009-11-16 13:05:14.407 2009-11-16 13:05:14.423

2009-11-16 13:05:22.283 2009-11-16 13:05:22.300

Yes its 100,000 batches and yes the Seconds components are still the same, but I only have so much playtime allowed.:-P

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

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

Lynn Pettis (11/17/2009)
Dietmar Weickert (11/17/2009)
... finally trying to argue that the whole thing were for educational purposes is even more cynical. I hope nobody ever had or will have a teacher trying to teach that way. Guiding students purposefully into the wrong direction may even be disastrous.
... if you take exception to that this is for educational purposes, then what is the QotD for in your opinion. It is my understanding that is exactly what QotD was meant to accomplish.

QOD is for educational purposes, but to use that purpose in defence of your (sorry, bitbucket's) question is not helpful. Neither is any reference to questioners having submitted questions themselves. Both only serve to distract.

The fact is that the question and answer as set are at best confusing, and if someone were to get the right answer, and goes on to expect SQL Server to return the same values for getdate() and getutcdate() outside the GMT zone (or even in the same sql statement), their education hasn't been helped.