Do you know your getdate()?

  • Of course, the returned values will vary here because this is not factoring in the execution time of the getdate() or systemgetdate() functions. They will each have a cost associated with each call, so the answer in this logic isn't so much just a question of the accuracy of the functions, but also the processing time for these calls. These will vary depending on the hardware of the machine that you are on and other activity on the system.

  • Thank you for the information we don't for the moment use the precision described in our day to day processing of data, but it is definitely something to keep in mind for the future.

  • ... Soooo how do we notify the article author that he was wrong?

  • I think the article is referring to the ACCURACY of the two functions, not to the precision.

  • Of course I meant systemdatetime()!

  • I don't know what is all this fuzz about ... The results are going to be very server-dependent you don't know what other activities are happening at the same time you are running these loops. In my opinion these tests really represent nothing.

    * Noel

  • Nope: Ran this on my 32-bit box and got:




    CPU: Xeon E5450 @ 3 GHz

    OS: 2003 Server, Standard Ed., R2, SP2

    SQL: Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Guess you'll have to re-apply for a job elsewhere now 😉

    Let us know when you've figured out the rationale for this varying result.....


  • Hi there,

    Windows 7 Professional, 32-bit operatibg system.

    Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7600: )



    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7600: )

    - getdate()






    José Cruz

  • Windows 2008

    SQL Server Enterprise 64bit 2008 sp1

    Getdate /2nd run

    88047 /113305

    4.8 /7.44


    127186 /137377

    3476.36 /6143.44

    Windows 2003

    SQL Server Enterprise 2008 sp1

    GetDate /2nd run

    19830 /36528

    3.367 /3.464


    127186 /8437

    3476.36 /15636

    Perhaps the measurements are affected by load on the server. I also notice that there is a CPU and network load on my client workstation from SSMS while the queries are running, could the client also be a factor?

  • Just a thought, but I also got a more accurate GETDATE return value than the original article.

    (19430 ,3.52769) I think that a lot of this has to do with what else is running on the SQL box.

    So the date accuracy will be dependent on the load of the server to a degree.

    At least that is what it seems like.

  • If someone asked me about the accuracy in an interview, my response would be "I don't know. I've never needed GetDate() or related functions to be that precise. If and when I do need to know, I'd look it up in Books Online."

    I'd probably be dinged in the interview, but at least I wouldn't be taking potshots and getting them wrong. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog:[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Hi there,

    Not that it really matters, but if i use the time difference every time getdate() returns a differente value, i reach to a better result.





    DECLARE @Count INT

    DECLARE @Difference INT

    SET @I = 0

    SET @Count = 0

    SET @Difference = 0

    SET @TimeStart = GETDATE()

    SET @Time = @TimeStart

    WHILE @I < 10000000


    SET @TimeEnd = GETDATE()

    IF @TimeEnd <> @Time


    SET @Count = @Count + 1

    SET @Difference = @Difference + DATEDIFF(millisecond, @Time, @TimeEnd)

    SET @Time = @TimeEnd


    SET @I = @I + 1


    PRINT @Count

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

    PRINT @Difference / CAST(@Count AS REAL)

    With this code i get the following result on my SQL2005 instance:




    José Cruz

  • Win Xp 32 bit SP3 (Under VMware Workstation)

    SQL Server 2008 (10.0.2531)

    Getdate(): 4291 values with precision 3.35027

    SysDateTime(): 1903 values with precision 10.0641

  • Chris Howarth-536003 (5/12/2010)

    This makes an interesting read, particularly regarding SQL 2005 SP3's attempts to lower granularity to 1ms, but the article doesn't specifically mention GETDATE():


    Chris, good link, and it basically describes the underlying problems that cause the ~16ms or 3.3 ms for getdate() and no better precision then 1 ms for sysdatatetime(). I guess some OS configurations expose the higher percision timer to SQL Server so this is why some people come with the 3.33ms for getdate(). It might be because of SP versions on the OS or SQL Sevrer and also the boot.ini switch. There is some indication in the article that Microsoft recently introduced some means of exposing a higher precision timers to the SQL Sevrer with the new service packs. That might explain why some people can get better results then the ~16ms I got in my tests.

    Most precision timers under Windows are tick counters and are good at calculating the time difference - not the current time. The getdate() and sysdatetime() functions are only as precise as the underlying OS timer.

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

  • thisisfutile (5/12/2010)

    thisisfutile (5/12/2010)

    JacekO (5/12/2010)

    If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version.

    My XP workstation is 32-bit with SQL Server 2008 Express (SP3) ... and I get 3.3333 on the first query

    My server is SBS 2003, 32-bit with SQL Server 2005 Workgroup (SP2) ... and I get 15.6xxx on the first query


    Expanding on my previous respons...

    If I run the first query directly on the server, it still reports 15.62xx. After seeing 3.33 on my workstation and it's local SQL install, I sort of expected to see 3.33 when running the query directly on the server. </naivety>


    I think you might be actually pointing out to the reason of the differing results. Your server is running SP2 and workstation is running SP3.

    The link Chris posted to the MS blog (see my reply above) indicates that MS introduced some changes in SP3 of 2005 so the server can use a higher precision timers. If the timer has better presision then 3.33 ms then the getdate will be limited by the datatype precision. If the server is using the lower precision timer then you will see the ~16ms getdate() precision.

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

Viewing 15 posts - 31 through 45 (of 63 total)

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