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),
i knew both function but even i used this,but i did not know exact difference,i searched on google and i found it here .
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.
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.
Nice to read this comment @SonTran
Thank you for pointing out subtle difference between GETDATE() and SYSDATETIME() and explaining it visually clear.