Do you know your getdate()?

  • Comments posted to this topic are about the item Do you know your getdate()?

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • thanks :-)... was really not aware of this stuff... also a good interview question...

  • Is there a reason you chose not to perform the same tests on the GETUTCDATE function?

  • Testing the precision of GETDATE()...

    instead of getting values 595 and 15.6521,

    i got

    4262 -out of 10 million calls i got distinct 4262 values returned by GETDATE()

    and

    3.33318 - what is the precision of the GETDATE() function

    which is supposed to be true.

    i ran the given query in the post, which the answer is supposed to be true.

    i ran the query against SQL SERVER 2008

    if i am wrong somewhere, let me know

  • Buddies,

    I am using SS2005 and I ran the first query: DECLARE @TimeStart DATETIME

    DECLARE @Time DATETIME

    DECLARE @TimeEnd DATETIME

    DECLARE @I INT

    DECLARE @Count INT

    SET @I = 0

    SET @Count = 0

    SET @TimeStart = GETDATE()

    SET @Time = @TimeStart

    WHILE @I < 10000000

    BEGIN

    SET @TimeEnd = GETDATE()

    IF @TimeEnd <> @Time

    BEGIN

    SET @Count = @Count + 1

    SET @Time = @TimeEnd

    END

    SET @I = @I + 1

    END

    PRINT @Count

    PRINT DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)

    I have got the values: 6519 and 3.33379.

    I ran the same query again to re-verify, but I have got: 6518 and 3.3363.

    The precision is 3.33 for GETDATE() function.

    How these values are vary (595 and 15.6521 - 6519 and 3.33379) ?

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • Same result ...

    15020 and 3.3462

    SQL 2005 Developer on Vista 64bit

    Edit:

    Same test on SQL 2008 Developer on the same machine

    2900 and 15.5941

  • I ran the same 2 tests on Win 7 64bit Quad core, 8GB Ram machine running SQL Server 2008 Developer 64bit.

    And got the following:

    GetDate(): 2604 values with precision 3.33333 milliseconds

    SysDateTime(): 13930 values with precision 1000 microseconds, (1 millisecond)

    Judging by the rest of the comments so far it seems to be that XP can't hack it whilst latest OS' can (unless CPU cores played a part).

    However in production you would never use XP, Vista or 7 to run your SQL Server so more interesting would be results on other Windows server versions.

  • Some more SYSDATETIME Results:

    SQL 2008 R2 on Windows 7 x64: 1 ms

    SQL 2008 R2 on Win 2008 R2 on HyperV on Win 2008 R2: 15.5 ms

    SQL 2008 R2 on Win 2008 R2 on VMWare on Win 7 x64: 1.3 ms

  • ... and another test

    Win XP 32 bit SP3

    SQL Server 2005 SP3 (9.0.4262)

    CPU 2 processors (Core duo)

    4517 and 3.32809

  • is the other instructions present in the code takes some millisecond ?

  • I get 3.3 ms here using your own code.

    The worst part about it is you would have not given the job to someone who correctly responded with 3.3ms for GETDATE() precision:-P

  • If you get 15.6521 it does not mean that this is the precision of getdate()!

    Why do you think that @Time and @TimeEnd have succesiv values?

    The conclusion, in my opinion, is the your system is very good (count is only 595 so only 595 times @Time and @TimeEnd are different, on my system is gets 18475), but the load of you system is very high, your processor is taken by another process from time to time so you get a very big value for the "precision" (in my case is 3.34344).

  • matthieu.ravard (5/12/2010)


    is the other instructions present in the code takes some millisecond ?

    Doesn't matter as long as you get several loop iterations before the timer gives another value.

  • WIN XP SP3 / SQL Server 2008 SP1 on dual core machine

    getdate: 6043 3.3399 & 5957 3.33557

    sysdatetime: 1713 15625 & 1789 15625

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • My results on a single Windows 2000 Professional SP4 machine:

    - In SQL Server 2000 Query Analyzer: did not run within 5 minutes runtime, I do not understand why.

    - In SQL Server 2000 SP4, via Access .ADP file: 1,418 / 15.62,

    - In SQL Server 2005 SP2, via Access .ADP file: 789 / 15.62.

    Hope, this adds to the discussion, Leendert.

Viewing 15 posts - 1 through 15 (of 63 total)

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