Post reply

GETDATE() puzzle

  • October 29, 2007 at 11:11 am

    #176615

    In 32-bit SQL 2000, I've been depending on GETDATE() to return an identical datetime value for each returned row in a SELECT statement that uses a GROUP BY.

    Upgrading to 64-bit SQL 2005, I'm getting a distinct datetime value for each row returned.

    Has anyone else run across this?

  • October 29, 2007 at 12:24 pm

    #746361

    2005 has a more granular time field internally. It deals in micro seconds instead of milli seconds. That might explain it. It still stores datetime fields as milliseconds. I had not seen this in result sets but I have seen it in Profiler.

    Depending on the size of the result set, I saw deviation even in 2000.

    Just declare a variable at the top of the query & use that with the result set if you need them all to be the same.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 29, 2007 at 12:32 pm

    #746367

    Thanks - that makes sense.

  • October 29, 2007 at 9:18 pm

    #746452

    The only way to guarantee the same time would be to store it in a variable and then do the SELECT using the variable instead of GETDATE().

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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