Wired result in two float number different result

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi All,
I find out wired result once i try to get the different in 2 float number. Here are the query ,
data and result which are i tired.

In SalesInvoices table have 2 float columns, one if GrandTotal and the other is RealAmountPaid.

GrandTotal =2306.28
RealAmountPaid= 2306.28

I write the query as below to get the different of these two columns,

SQL

SELECT GrandTotal, RealAmountPaid,(GrandTotal - RealAmountPaid) As NetDueAmount
FROM SalesInvoices

Here are the wired result what i got
GrandTotal =2306.28
RealAmountPaid = 2306.28
NetDueAmount = 4.54747350886464E-13

I found that the other number result are correct.
eg.1
GrandTotal =299.6
RealAmountPaid=250
NetDueAmount =49.6

GrandTotal =1004.73
RealAmountPaid=1004.73
NetDueAmount =0

Did anyone faced the same problem?
Kindly guide me if you found any error in my query or data structure.

Thanks in advance,
Theingi

Posted 8-Apr-14 17:43pm

Theingi Win

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Homero Rivera · Accepted Answer · 2014-04-08T17:58:00

Solution 1

Your query is good, your selection of data types not.

FLOAT should not be used for currency, nor REAL.
You should use MONEY or DECIMAL.
Float uses scientific approximations and stuff, it is good for sampling and math.

You can't change that behaviour you're getting.

http://msdn.microsoft.com/en-us/library/ms173773.aspx[^]

PS Notice that 4.54747350886464E-13 means...
0.00000000000454747350886464
See what I mean by 'scientific approximations'?
FLOAT and REAL are good, but not for your particular problem.

I had the same problem as you in the past, only I was measuring how many kilos of tortillas were being used at a restaurant.

Posted 8-Apr-14 17:58pm

Homero Rivera

Updated 8-Apr-14 18:10pm

v4

Comments

Bernhard Hiller 9-Apr-14 3:46am

Alternatively, integer datatypes could do also, then store the amount in cents instead of dollars.

Homero Rivera 9-Apr-14 19:51pm

True!

Theingi Win 9-Apr-14 5:01am

thanks Homero Rivera.
The reason why we used the float instead of the decimal is we don't want to control the decimal place.
If we used the Decimal , we need to control the decimal point 2 or 3.In my environment those value decimal point can be 2 or 3.
May i know how did you control for this.

Homero Rivera 9-Apr-14 19:51pm

I had to use other datatypes such as money or bigmoney. that's the only way to prevent such behaviour. Float and Real will always bring these 'discrepancies'.

Homero Rivera 9-Apr-14 19:56pm

Also... If the answers you get are correct, I suggest you make a habit of accepting as answer. Thanks