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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL