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),
59 Comments. Leave new
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?
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!
I was unaware of the second function SYSDATETIME(). Thanks for posting. and again same question: I wonder how did you come across this?
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.
I did not know the difference between that sentences. Thanks for the info.
Regards.
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.
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
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
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
As per BOL, the return type of SYSDATETIME is datetime2(7)
Also the query only works from version 2008 onwards only
Wow, did not know this. I wonder if there is ever a need or use for such precision in a stored procedure?
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.
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
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….
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.
Thanks Pinal,
No i didn’t know this.
Thanks for Sharing.
Regards,
Nitin Sharma
Hi.
I knew :-)
I use sysdatetime when inserting rows in a log table. I need sysdatetime’s precision in that case.
Regards
Børre Dalhaug
Hi Pinal,
Thanks. Even i dont know the SYSDATETIME function in SQL.
Can you tell us more about the unknown SQL functions.
Hi pinal,
Thanks for the article…truly speaking even i never knew about this.
Hi Pinal,
Thanks. Even i dont know the SYSDATETIME function in SQL.
Can you tell us more about the unknown SQL functions.