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