Home Dashboard Directory Help
Search

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


Status: 

Active


5
0
Sign in
to vote
Type: Bug
ID: 867436
Opened: 5/5/2014 3:59:10 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft 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.

Thanks,
Jos de Bruijn - SQL Server PM
Posted by Microsoft 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
Sign in to post a workaround.