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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |