SQL 2012 datetime rounding issue (SQL Server DBA)

Search sqlserver-dba.com

Follow sqlserver-dba.com

Email Jack Vamvas at jack@sqlserver-dba.com

Daily & Exclusive Content

SQLServer-DBA.com Links

Recent Posts

SQL 2012 datetime rounding issue

12 March,2014 by Jack Vamvas

When upgrading some SQL Server 2005 databases to a SQL Server 2012 instance , I spotted some datetime rounding behaviour, which I hadn't noticed before - but has existed since SQL 2000!

The problem manifested itself when the SQL Server 2012 instance received a value from the application . In Example 1 below - The application was using an input parameter value '2014-03-12 13:09:56.341' , but it was being rounded to ‘2014-12-03 13:09:56.340’. The input parameter was defined as a datetime data type.

A potential solution is changing the input parameter to a data type of datetime2(3) , which has accuracy to 100ns and increased precision, solved the problem. The datetime2 data type was introduced in SQL 2008, Check Example 2 below. Note: I added (3) as fractional seconds precision – which has the effect of trimming the output

Datetime values are rounded to increments of .000, .003, or .007 seconds, by using datetime2 with the increased precision , allows the input value to be preserved at a higher level of precision

--rounded to .000 ,.003 , .007 increments
declare @d1 datetime 
set @d1 = '2014-03-12 13:09:56.341';
select @d1;
--returns - 2014-03-12 13:09:56.340

--up to 7 decimal places - accurate to within 100ns
declare @d2 datetime2(3);
set @d2 ='2014-03-12 13:09:56.341' ;
select @d2;
--returns - 2014-03-12 13:09:56.341

SQL Server - Get datetime values with agent_datetime function and msdb..sysjobhistory

Arithmetic overflow error and isdate sql

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Share:

Posted by Jack Vamvas at 7:25 AM in SQL problems, T-SQL | Permalink | Comments (2) | TrackBack (0)

Searches: datetime precision, datetime rounding, datetime2 rounding, Rounding of datetime Fractional Second Precision

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

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:

Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.