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
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.
No i didn’t know this, thanks for sharing …
Please refer this
No dint know.. :( TFS..
NO..I DONT KNOW…
THANKS FOR SHARING SIR…
-SKC
Thanks for Sharing Sir….Gr8….!
Thank for your post
I’m searching the right function for setting datetime2 and your post give me the answere.
You can use these functions when you generate time related reports like employee working hour report or for logs….
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.
I really don’t know this. thanks a lot and also this sites help me a lot when i am in difficult situation.
Thanks
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
Thanks Pinal. Really its good.
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
Thanks
Wonderful article..
Hi , i want date as
jan twenty eighth 2001 in ms sql.
If you want to show formatted dates in front end application, do the formation there
Thanks Pinal…
Your blog is very useful…..
Thanks pinal…..was totally unaware of sysdatetime…..
Its very Nice article, i don’t know about this before, now i am clear…
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?
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 .