Query results for floating point operations depend on the execution plan of the query - by GrzegorzŁyp

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 867436 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 5/5/2014 3:59:10 AM
Access Restriction Public


Depending on the query plan of the query SQL Server returns different results of floating point operations. This Bug is repoduced on SQL 2008, SQL2012, SQL2014.
Sign in to post a comment.
Posted by Jos [MSFT] on 5/8/2014 at 2:58 PM
The root cause of the wrong result (7:59) is that SQL uses FLOAT for conversion from numeric to datetime, and floating point arithmetic is inherently imprecise.
The fact that in some cases you get the correct result (8:00) is due to an auto-parameterization issue which can slightly change the data type (but will not cause incorrect results for precise types). We have chosen not to address this issue, as it has been in the product for many releases, and there are potential back-compat concerns.

All that said, in general we recommend *not* to convert numeric to datetime values.
Instead, use the SQL2008 data types date and datetime2, along with intrinsics such as datefromparts to construct date/datetime2 values.

Jos de Bruijn - SQL Server PM
Posted by Jos [MSFT] on 5/7/2014 at 1:11 PM
Thanks for filing this bug. We can reproduce this locally, and we are investigating.

Jos de Bruijn - SQL Server PM