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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
59 Comments. Leave new
getdate () 2014-12-31 13:12:19.000
SYSDATETIME() 2014-12-31 13:12:19.0003601
Difference is after the miliseconds getdate() only 3 digit but sysdatetime() above three digit
Thanks for the comment. My question was “did you know this before reading this blog :)”
GETDATE returns a datetime datatype which only has precision of 3-4 ms whereas SYSDATETIME() returns a datetime2(7) datatype.
Correct sivasai
i just got to know but for more details i found out your blog and we are on same page :) Thx Pinal!
Thanks you for sharing the info. Yes, I too don’t know the difference before. Could you please provide the information on when to use GETDATE() and SYSDATETIME() functions?
I just discovered why I need getdate() and not sysdatetime(). I needed to determine that line items on a customer order were updated as part of a single event. Our programmer originally used sysdatetime() which proved too accurate and the line items could would sometimes look like they were two different update events. I asked him to change to getdate() and this fixed this problem.
I knew there must be a difference.
but didnt know the difference AT ALL. thanks
I did very recently.
However, it has made my rating of this site increase dramatically as I have a massive amount of respect for anyone, however senior they are in their field, that can admit that they didn’t know something – as that is all of us – I know I learn something new every day.
Thanks for sharing!
@Nathan – Thanks for being honest.
I didn’t know the difference and I was trying to decide if my using GETDATE() was incorrect for a particular use in a trigger and stored procedures. Because of your answer, I can now safely say I want GETDATE() and not the other way around. Thanks!
Thanks for sharing your thoughts Rebecca.
Great blog, I didn’t know but should haven’t know better since each return different type. I always learn something new with your blog.
Thank you for pointing out subtle difference between GETDATE() and SYSDATETIME() and explaining it visually clear.