SQL 2012 datetime rounding issue

Jack,

Good article, and a few comments.

The datetime "rounding error" issue you mention is not new to SQL 2012, but has been a characteristic of the datetime data type (as you mentioned) for some time, and is present in prior versions of SQL Server (including in SQL 2005 that was the source system of your migration). I have tested and confirmed this behavior in SQL 2000, 2005, 2008, and 2008 R2, as well as SQL 2012 as you mentioned.

The solution you propose is the behavior of the datetime2 data type, and applies to SQL Server 2008 and later versions (SQL 2008 was the first version to support the datetime2 data type).

What threw me off at first in your post was the rounded result also transposed the month and day values of the dates (input date of 2014-03-12 became result date of 2014-12-03), which would not be a rounding issue, but a date format locale issue were it the case). Runs of your test scripts show that the transposed month / day numbers in your post text and in the script comments were just a minor typo (input and result dates from test runs were the same, with no transposition), and not related to the fractional seconds rounding issue you were discussing.

Scott R.

Posted by: Scott R. | Mar 12, 2014 3:59:48 PM

Hi Scott, thanks for you comments.
I thought it was obvious from the post that the datetime rounding characteristic existed prior to SQL 2012, thank for reiterating.
The main point I was trying to make - was the use of datetime2 to create a workaroud.
I've fixed the typo -

Posted by: Jack Vamvas | Mar 13, 2014 7:04:05 AM