T-SQL

  • 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'

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.


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

  • 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?

  • 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

    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
  • 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()))

  • 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.

  • I did catch the difference. But thought we are not going anywhere by these discussions.

  • 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.

  • 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

  • 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

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