SQL 2012 datetime rounding issue

12 March,2014 by Tom Collins

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

Read More

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

Arithmetic overflow error and isdate sql

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

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.


Post a comment on SQL 2012 datetime rounding issue

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer