CODEWITHSUNDEEP
sqlsql-serverroundingcurrency
GregM
GregM

Reputation: 2654

sql rounding not always rounding up

I'm trying to do a round in sql but the result is very strange; here is what i do :

I'm using Mssql

I have a select :

Select num1, num2, num1 * num2, round(num1 * num2, 2)
from myTable

Datatype :

num1 is a float

num2 is a money

if the value of num1 is 15 and the value of num2 is 0.033 Here is my resultset :

15, 0.033, 0.495, 0.5

The problem is when I have those values :

if the value of num1 is 5 and the value of num2 is 0.045 Here is my resultset :

5, 0.045, 0.225, 0.22

Someone have an idea?

Upvotes: 4

Views: 4640

Answers (4)

GregM
GregM

Reputation: 2654

I've converted my float column to money and it's seems to be working now.

Thanks all of you !

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89741

If you look at this runnable example in grid view mode (note also that text mode differs), you should see the problem:

DECLARE @tbl AS TABLE (num1 float, num2 money);
INSERT INTO @tbl VALUES (15, 0.033), (5, 0.045);

Select num1, num2, num1 * num2, round(num1 * num2, 2)
from @tbl;

Note that the number is actually .224999999, which rounds down to .22 - you need to be careful when looking at the non-binary output of SQL Server - it sometimes converts numbers to text in ways you are not expecting.

This comes from float * money operation resulting in a float which is subject to binary representation issues.

You should consider what it means for your float value and consider using decimal instead, and look at the rules for scale and precision for decimal operations. Note that money is only somewhat similar to decimal(19, 4)

Upvotes: 5

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

What version of SQL are you using?

Try this code in Query Analyzer


DECLARE @num1 float
DECLARE @num2 money

SET @num1 = 15
SET @num2 = 0.033

SELECT
@Num1,
@Num2,
@Num1 * @Num2,
ROUND(@Num1 * @Num2,0),
ROUND(@Num1 * @Num2,1),
ROUND(@Num1 * @Num2,2),
ROUND(@Num1 * @Num2,3)

Float is an Approximation not a real number. Use Decimal instead.


DECLARE @num1 decimal
DECLARE @num2 money

SET @num1 = 5
SET @num2 = 0.045

SELECT
@Num1,
@Num2,
@Num1 * @Num2,
ROUND(@Num1 * @Num2,2),
ROUND(CAST(@Num1 as money) * @Num2,2)

Upvotes: 0

JanT
JanT

Reputation: 2096

You could try to use long type instead of float. I think this is accuracy issue with floats.

Upvotes: 1

Related Questions