Different Data type cause different result at MS SQL

April 17, 2017 at 18:47:09
Specs: Windows 7, i5 2520M / 8 GB
My AP system use many SP, I find a problem at the computing issue about data type at MS SQL

The issue as below:

DECLARE @R_N DECIMAL(38,18)
DECLARE @M_N DECIMAL(38,18)
DECLARE @E_N DECIMAL(38,18)
DECLARE @W_N DECIMAL(38,18)

DECLARE @W_F FLOAT

SET @R_N = 0.02
SET @M_N = 0.178135
SET @E_N = 680520.886354759

SET @W_N = (CAST(@R_N AS DECIMAL(38,18)) * CAST(@M_N as DECIMAL(38,18))) * CAST(@E_N AS DECIMAL(38,18))

SET @W_F = (CAST(@R_N AS float) * CAST(@M_N as float) * CAST(@E_N AS float))

PRINT CAST(@W_N AS DECIMAL(38,18))
PRINT CAST(@W_F AS DECIMAL(38,18))

The Result is as below:
@W_N = 2424.695918000000000000
@W_F = 2424.491761816099800000

MS SQL version is 2012

I test this result at EXCEL, and get the result 2,424.4917618161000, It seems the Excel result and @W_F calculations are similar.

My question is why Different Data type cause different result? As the above test, Do we change the data type at my AP system?


See More: Different Data type cause different result at MS SQL

Reply ↓  Report •

#1
April 17, 2017 at 19:23:18
Decimal is strictly base10. Float is base2, which is less accurate but also the base the computer thinks in.

How To Ask Questions The Smart Way


Reply ↓  Report •

#2
April 18, 2017 at 02:37:37
Do I use the FLOAT data type?

Reply ↓  Report •

#3
April 18, 2017 at 05:09:57
It would depend on your need for accuracy, and what you intend to do with them.

How To Ask Questions The Smart Way


Reply ↓  Report •
Related Solutions


Ask Question