Getting and storing a high precision datetime

5

August 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.

5 thoughts on “Getting and storing a high precision datetime

  1. notarian says:

    I have an imaginary Kendra Little on my shoulder reminding me that datetime is deprecated and I should always be using datetime2.

  2. Phil says:

    Those are also ANSI standard, where getdate() is vendor specific, right? Man I’m so lazy. But I think that’s the case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: