Getting and storing a high precision datetime
5August 13, 2015 by Kenneth Fisher
Earlier today I was working on a project where I was recording some time stamps. In order to get as much precision as possible I decided to go with a datetime2 datatype. Unfortunately when I recorded the time stamp it didn’t quite work out liked I’d hoped.
DECLARE @StartDate datetime2 = getdate() PRINT @StartDate
Which returned:
2015-07-08 21:57:07.8670000
That’s an aweful lot of zeros at the end isn’t it? It didn’t take me long to realize that I needed something other than getdate(). After a bit of research I found sysdatetime().
DECLARE @StartDate datetime2 = sysdatetime() PRINT @StartDate
Which returned:
2015-07-08 22:09:28.6553667
So the moral of the story is that when using one of the high precision date/time datatypes (time, datetime2, datetimeoffset) you need to use one of the high precision date/time functions (SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, TIME).
For a quick review of the date time datatypes look here and for the date time functions here.
SQL Studies 
I have an imaginary Kendra Little on my shoulder reminding me that datetime is deprecated and I should always be using datetime2.
I didn’t realize that datetime was deprecated? I see where they recommend not using it for future stuff but not that it was deprecated. I just hope if/when it is removed they change the existing date functions (getdate()) etc to have the additional precision.
You are right. The imaginary Kendra is a liar. The real one is not. http://www.brentozar.com/dates-times-sql-server/
Those are also ANSI standard, where getdate() is vendor specific, right? Man I’m so lazy. But I think that’s the case.
Probably. I should worry more about ANSI standard but I really don’t unless I know I need to. 🙂