From SQL Server 2008, the datetime2 datatype was introduced, with greater flexibility for precision and also with an added bonus that it was truly compliant with ANSI and ISO 8601.  For more info, read up on the Books Online entry for datetime2.

While not everybody (including Microsoft!) have adopted this datatype, if you really want accuracy for your datetime columns, you’re better off switching to this type, and I know quite a few implementations that do.

To truly take advantage of this type, though, you need to switch from using GETDATE() to SYSDATETIME() (or their UTC variants).  The reason is that GETDATE() uses the old datetime type, whilst SYSDATETIME() uses the new datetime2(7) type.  The difference is actually quite significant:-



If you care about those milliseconds and microseconds (and if you’re using datetime2 you probably do), then that’s quite a significant rounding error caused by using GETDATE().  In one example, where the table in question was processing messages and logging them ordered by date, the lack of accuracy from GETDATE() made it appear that some messages that were actually processed in a sequence (and actually out of the desired sequence due to an issue with parallel message queues) were all logged with the same time; making troubleshooting the issue with the message queues much more difficult to resolve.  And if that sequence is important (and if you’re using a message queue, it probably is; in the example it was) this could be a very serious issue.

So, if you’re going to use datetime2, which you should, then stop using GETDATE() and switch to SYSDATETIME()!


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s