November 17, 2009 at 11:22 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 think everyone agrees that the times are logically equivalent.
However, they are not actually equivalent because they do not have the time zone appended.
Anyone looking at those values without seeing the actual SQL would say the values are not equal.
If the time zone were appended, I would agree with your point.
As it happens, SQL Server does not recognize GetDate() and GetUtcDate() as being equal.
Take a look at this:
IF GETDATE() = GETUTCDATE()
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
IF GETDATE() = GETDATE()
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
November 17, 2009 at 11:35 am
david.wright-948385 (11/17/2009)
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.
I don't expect getdate() and gtutcdate() to return the same time. They return the same equivalent time. Again, 8:00 AM MST == 3:00 PM UTC.
November 17, 2009 at 11:39 am
I GOT it!
The query should have been written like this instead of as four separate queries:
SELECT
DATEADD(day, -1, GETDATE()),DATENAME (dw ,DATEADD(day, -1, GETDATE()) ),
GETDATE() - 1, DATENAME (dw ,GETDATE() - 1),
GETUTCDATE() - 1, DATENAME (dw,GETUTCDATE() - 1),
DATEADD(day, -1, GETUTCDATE()), DATENAME (dw,DATEADD(day, -1, GETUTCDATE()));
Now, does that satisfy everyone? GETDATE() and GETUTCDATE() will return the equivalent date/time when this query runs.
November 17, 2009 at 11:42 am
Lynn Pettis (11/17/2009)
I don't expect getdate() and gtutcdate() to return the same time. They return the same equivalent time. Again, 8:00 AM MST == 3:00 PM UTC.
Exactly. So you just need to change 'same' in your question to 'equivalent' and cache the datetime values. Otherwise it's fine.
November 17, 2009 at 11:44 am
Lynn Pettis (11/17/2009)
I GOT it!The query should have been written like this instead of as four separate queries:
SELECT
DATEADD(day, -1, GETDATE()),DATENAME (dw ,DATEADD(day, -1, GETDATE()) ),
GETDATE() - 1, DATENAME (dw ,GETDATE() - 1),
GETUTCDATE() - 1, DATENAME (dw,GETUTCDATE() - 1),
DATEADD(day, -1, GETUTCDATE()), DATENAME (dw,DATEADD(day, -1, GETUTCDATE()));
Now, does that satisfy everyone? GETDATE() and GETUTCDATE() will return the equivalent date/time when this query runs.
Nope. Does not satisfy me at all. If the time zone or offset is not listed with the time then these dates/times are not equivalent except when run in Greenwich, England.
November 17, 2009 at 11:47 am
Now, does that satisfy everyone? GETDATE() and GETUTCDATE() will return the equivalent date/time when this query runs.
Lynn Pettis
It solves the time difference problem due to the 4 separate queries.
I notice you use the term equivalent vs. the term same.
So it the original question had used the term equivalent, we wouldn't be discussing this at length.
November 17, 2009 at 11:50 am
I really missed something else, but learned something as well just now.
1) Multiple calls to getdate() in the same query will return the same value.
2) Multiple calls to getutcdate() in the same query will return the same value.
3) Multiple calls to getdate() and getutcdate() don't (as seen by a slight variation in the milliseconds.
4) DATEADD(dd, -1, GETDATE()) and GETDATE() -1 return the same values.
5) DATENAME (dw ,DATEADD(day, -1, GETDATE()) ) and DATENAME(dw, GETDATE() - 1) return the same values.
6) DATEADD(dd, -1, GETUTCDATE()) and GETUTCDATE() -1 return the same values.
7) DATENAME (dw ,DATEADD(day, -1, GETUTCDATE()) ) and DATENAME(dw, GETUTCDATE() - 1) return the same values.
Now I do have a question, what was the question actually being asked? Before reading this last post, how many of you had caught what I just caught?
November 17, 2009 at 11:51 am
As it happens, SQL Server does not recognize GetDate() and GetUtcDate() as being equal.
Take a look at this:
IF GETDATE() = GETUTCDATE()
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
IF GETDATE() = GETDATE()
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
By that logic, SQL Server doesn't always recognize GETDATE() and GETDATE() as being equal.
The following code usually returns 'FALSE' within 1 second.
DECLARE @DUMMY varchar(10)
WHILE 1 = 1
BEGIN
IF GETDATE() = GETDATE() BEGIN
SET @DUMMY = 'TRUE'
END
ELSE BEGIN
SET @DUMMY = 'FALSE'
BREAK
END
END
PRINT @DUMMY
November 17, 2009 at 11:52 am
Because, I can also write the queries like this:
SELECT
DATEADD(day, -1, GETDATE()),DATENAME (dw ,DATEADD(day, -1, GETDATE()) ),
GETDATE() - 1, DATENAME (dw ,GETDATE() - 1)
SELECT
GETUTCDATE() - 1, DATENAME (dw,GETUTCDATE() - 1),
DATEADD(day, -1, GETUTCDATE()), DATENAME (dw,DATEADD(day, -1, GETUTCDATE()))
November 17, 2009 at 11:55 am
I would consider them the "same" if I could use either one in my code and receive the same results. However, queries like the following:
select datediff(hh, '2009-11-17 7:57:00', GETDATE())
select datediff(hh, '2009-11-17 7:57:00', GETUTCDATE())
result in different values. It doesn't matter that the datestamp stored by SQL is the same, or that the times are equivalent but in different time zones. I cannot interchange the two functions and receive the "same" results.
November 17, 2009 at 11:56 am
I did catch the difference. But thought we are not going anywhere by these discussions.
November 17, 2009 at 11:57 am
Tom Garth (11/17/2009)
As it happens, SQL Server does not recognize GetDate() and GetUtcDate() as being equal.
Take a look at this:
...
Your correct GETDATE() <> GETUTCDATE(), but GETDATE() and GETUTCDATE() can represent the same moment in time, 8:00 AM MST == 3:00 PM UTC.
November 17, 2009 at 11:58 am
Goldie Graber (11/17/2009)
Lynn Pettis (11/17/2009)
I GOT it!The query should have been written like this instead of as four separate queries:
SELECT
DATEADD(day, -1, GETDATE()),DATENAME (dw ,DATEADD(day, -1, GETDATE()) ),
GETDATE() - 1, DATENAME (dw ,GETDATE() - 1),
GETUTCDATE() - 1, DATENAME (dw,GETUTCDATE() - 1),
DATEADD(day, -1, GETUTCDATE()), DATENAME (dw,DATEADD(day, -1, GETUTCDATE()));
Now, does that satisfy everyone? GETDATE() and GETUTCDATE() will return the equivalent date/time when this query runs.
Nope. Does not satisfy me at all. If the time zone or offset is not listed with the time then these dates/times are not equivalent except when run in Greenwich, England.
There is another reason why this cannot by satisfactory. Here is the script to catch the end of the second and then run the batch given in QoD. Even without looking at the hour difference due to the time zone offset, the seconds part is indeed different:
declare @start int;
set @start = 0;
while @start < 997
set @start = datepart(millisecond, getDate());
go
SELECT
DATEADD(day, -1, GETDATE()),DATENAME (dw ,DATEADD(day, -1, GETDATE()) ),
GETDATE() - 1, DATENAME (dw ,GETDATE() - 1),
GETUTCDATE() - 1, DATENAME (dw,GETUTCDATE() - 1),
DATEADD(day, -1, GETUTCDATE()), DATENAME (dw,DATEADD(day, -1, GETUTCDATE()));
go
the result:
2009-11-16 12:50:31.997 Monday 2009-11-16 12:50:31.997 Monday 2009-11-16 18:50:32.007 Monday
The seconds part is surely different: 31 in first and third and 32 in fifth and seventh columns.
Oleg
November 17, 2009 at 11:58 am
Lynn Pettis (11/17/2009)
I really missed something else, but learned something as well just now.1) Multiple calls to getdate() in the same query will return the same value.
2) Multiple calls to getutcdate() in the same query will return the same value.
3) Multiple calls to getdate() and getutcdate() don't (as seen by a slight variation in the milliseconds.
4) DATEADD(dd, -1, GETDATE()) and GETDATE() -1 return the same values.
5) DATENAME (dw ,DATEADD(day, -1, GETDATE()) ) and DATENAME(dw, GETDATE() - 1) return the same values.
6) DATEADD(dd, -1, GETUTCDATE()) and GETUTCDATE() -1 return the same values.
7) DATENAME (dw ,DATEADD(day, -1, GETUTCDATE()) ) and DATENAME(dw, GETUTCDATE() - 1) return the same values.
Now I do have a question, what was the question actually being asked? Before reading this last post, how many of you had caught what I just caught?
Lynn,
Statements 1 and 2 are not always true. From my earlier post:
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
November 17, 2009 at 12:01 pm
It should be false. They are the same only if you live in Greenwich England or your servers are set to Zulu time.
Viewing 15 posts - 91 through 105 (of 203 total)
You must be logged in to reply to this topic. Login to reply