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),

About these ads

49 thoughts on “SQL SERVER – Difference Between GETDATE and SYSDATETIME

  1. I didn’t know that and I’m kind of glad I haven’t run into a situation where it has mattered. This is good info to keep in mind in case I need this. How did you ever come across this?

  2. Here is a guess, GETDATE is time off the DC oppose to the server time??? Your intro sentence is dead on.

    I have enjoyed your blog for several years, thanks for your dedication!

  3. I was unaware of the second function SYSDATETIME(). Thanks for posting. and again same question: I wonder how did you come across this?

  4. Until SQL Server 2005, there was only one function, GETDATE() which returns a DATETIME value. A DATETIME value has precision upto 3 milliseconds.

    SQL Server 2008 introduced a new data type DATETIME2 which gives precision upto 100 nanoseconds. But if you use the GETDATE() function and assign the result to a DATETIME2 value, you would loose the precision (nanoseconds).

    So a new function was introduced which returns a DATETIME2 data type – having precision upto 100 nanoseconds.

    The major difference is the return value TYPE of each function. One returns DATETIME and the other DATETIME2.

  5. Well, I knew that there is some differencies between functions and datetime data types as Jacob described above, but I wasn’t aware of this particular one.

    I’ve took a habit of consulting MSDN every time I have doubts about something. For example I check built-in function parameters every time from MSDN. That is an easy way to find out things like this.

  6. great Pinal!

    I have never come across it. Thanks a lot for your contribution. I have learnt a lot from this blog.

    Another question.

    Pinal I want to appear in 70-432 exam,
    I have ready two, three relevant books, one books designed by Syngress another by Microsoft for this certification.

    Can you suggest a book which can help me pass the exam.

    What about brain dumps for this exam.

    Thank You.

    Tahir

  7. Hi,
    i have searched about it and concluded Mr. Jacob Sebastian is correct.
    But please any one mention the practical use of it

    thanks,
    biju

  8. That is a good find pinal. I had Never come across this function. It would be good if someone points out the good use of this function. Most of time we use GetDate() function, I guess this function(SYSDATETIME) might be usefull if you have any time sensitive data…

    Thanks
    Harsha

  9. RESPECTED SIR,
    I WANT TO BE A MEMBER OF YOUR COMMUNITY. PLEASE ALLOW ME TO JOIN AS IT WILL HELP IN MY STUDIES AS I AM A STUDENT OF MCA.
    THANKS & REGARDS
    KAMAL KANT.

  10. I didn’t know either, just never used the second one, but any way, you can’t store in the database milliseconds with precision greater than .000 and with step less than .003

  11. Pingback: SQL SERVER – Difference Between DATETIME and DATETIME2 Journey to SQL Authority with Pinal Dave

  12. hi, just never noticed it, but after trying it out where really it would be helpful, most of them that i use make use of the datetime data type and i can use getdate() with it and not sure where i can make use of the sysdatetime….

  13. Really not used SYSDATETIME since the need for such precision is not there for my line of business. It is interesting to note that such precision is available.

  14. Pingback: SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE Journey to SQL Authority with Pinal Dave

  15. Hi Pinal,
    Thanks. Even i dont know the SYSDATETIME function in SQL.
    Can you tell us more about the unknown SQL functions.

  16. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  17. Pingback: SQL SERVER – Information Related to DATETIME and DATETIME2 Journey to SQL Authority with Pinal Dave

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

  19. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  20. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

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

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

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

    Thanks

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

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

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s