SQL SERVER – Difference Between GETDATE and SYSDATETIME

Sometime something so simple skips our mind. I never knew the difference between GETDATE and SYSDATETIME. I just ran simple query as following and realized the difference.

SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime

In case of GETDATE the precision is till miliseconds and in case of SYSDATETIME the precision is till nanoseconds.

Now the questions is to you – did you know this? Be honest and please share your views. I already accepted that I did not know this in very first line.

This applies to SQL Server 2008 only.

Reference: Pinal Dave (http://www.SQLAuthority.com),

,
Previous Post
SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution
Next Post
SQL SERVER – Difference Between DATETIME and DATETIME2

Related Posts

59 Comments. Leave new

  • The company I work for has updated to SS2008 but still wants to use SSMS 2005. Access 2003 and Excel 2003 is involved also. Datetime2 is the main issue

    Acces and Excel convert the dates to text. and using native 2005 T-sql I have to convert the date to text to extract it. There are hundreds of reports that are affected. Fortunately 2008 this is still running in test.

    Woud upgraning to Access and Excel 2010 and SSMS 2008 allow reading of the datetime2 field as a date?

    I don’t want to get into a power struggle and try to force them to change all the datetime2 fields to datetime. We don’t nor do we intend to use anything less than 1/100 of a second.

    Reply
  • Nice. New to the SSMS 2008. Thanks

    Reply
  • Varinder Sandhu
    August 16, 2011 11:46 am

    No i didn’t know this, thanks for sharing …

    Please refer this

    Reply
  • Arunkarthik Viswanath
    September 5, 2011 6:53 pm

    No dint know.. :( TFS..

    Reply
  • Srinivas Kadiyala
    September 21, 2011 11:40 pm

    NO..I DONT KNOW…
    THANKS FOR SHARING SIR…
    -SKC

    Reply
  • Thanks for Sharing Sir….Gr8….!

    Reply
  • Thank for your post
    I’m searching the right function for setting datetime2 and your post give me the answere.

    Reply
  • Priyank sheth
    April 2, 2012 8:31 pm

    You can use these functions when you generate time related reports like employee working hour report or for logs….

    Reply
  • I just ran into a problem where I got this exception
    “The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value”
    So I looked at you description of datetime2 and datetime and came here to see the real diffrence.

    Thanks.

    Reply
  • kirti panchal
    June 29, 2012 10:31 am

    I really don’t know this. thanks a lot and also this sites help me a lot when i am in difficult situation.

    Thanks

    Reply
  • Manesh Joseph
    August 8, 2012 12:41 am

    I was also not aware of this. While trying this function, I came across another function called SYSDATETIMEOFFSET in which the your time zone offset is also included.

    select SYSDATETIMEOFFSET()

    Result : 2012-08-07 15:06:55.4932851 -04:00

    Reply
  • Thanks Pinal. Really its good.

    Reply
  • Didn’t know… Thanks! Any idea why the time from getdate() and sysdatetime() are so different? SQL 2008 they often differ by .010 seconds, see this example:
    PRINT DATEDIFF(ms,GETDATE(),SYSDATETIME())
    WAITFOR DELAY ’00:00:00.01′
    go 50

    Reply
  • Thanks

    Reply
  • Wonderful article..

    Reply
  • Veerabhadrarao Samudrala
    January 28, 2013 12:17 pm

    Hi , i want date as
    jan twenty eighth 2001 in ms sql.

    Reply
  • Thanks Pinal…
    Your blog is very useful…..

    Reply
  • rahul sahay (@rahulsahay123)
    March 1, 2013 11:23 am

    Thanks pinal…..was totally unaware of sysdatetime…..

    Reply
  • Its very Nice article, i don’t know about this before, now i am clear…

    Reply
  • Thanks Pinal, you blog posts have been very helpful. And no I didnt realize this difference till now. If that level of precesion is not needed would you still recommend the use of Getdate?

    Reply

Leave a Reply Cancel reply

Exit mobile version