Bug with Round with 2 decimal

Question

Bug with Round with 2 decimal

dquirion78

Ten Centuries

Points: 1240
More actions
January 21, 2014 at 2:01 pm

#303142

Hi l've a small bug with my formula
price : float type
qty : int type
rate : float type
formula : round(round (price* Qty,2) * rate ,2)
round( round(71.97 *1, 2) * 1.5) should give a result= 107.96 (107.955 rounded)
but it give
107.95 instead
Why wrong with my formula ?
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

dquirion78 (1/21/2014)
Hi l've a small bug with my formula
price : float type
qty : int type
rate : float type
formula : round(round (price* Qty,2) * rate ,2)
round( round(71.97 *1, 2) * 1.5) should give a result= 107.96 (107.955 rounded)
but it give
107.95 instead
Why wrong with my formula ?
thanks

The problem is you are using floats. Floats are approximate numbers. The problem with floats becomes very obvious when you start doing multiplication/division that results in fractions.

Here is your example using floats. This will in fact return 107.95

declare @price float = 71.97,

@qty int = 1,

@rate float = 1.5

select round(round(@price * @qty, 2) * @rate, 2)

If you change this same code to use numeric datatypes it works exactly as you are expecting.

declare @price numeric(9,2) = 71.97,

@qty int = 1,

@rate numeric(9,2) = 1.5

select round(round(@price * @qty, 2) * @rate, 2)

Moral of the story...don't use floats if you want precise values.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Using float in a calculation can cause problems because it is base 2 internally, so there can be issues with casting to display in base 10. For business calculations, it is best to use numeric data types.

In the example below, the intermediate result is cast to numeric before rounding, and gives the result you wanted.

declare @price float = 71.97

declare @qty int = 1

declare @rate float = 1.5

select Result=round(convert(numeric(10,5),@price*@Qty*@rate),2)

Results:

Result

---------------

107.96000

dquirion78 Ten Centuries Points: 1240 More actions · Answer 3

thanks !

I just read about money and decimal (19,4) Nobody seems to have the same answer about what the best data type...

dquirion78 Ten Centuries Points: 1240 More actions · Answer 4

Btw I've converted an access db to sql server and SSMA access convert to float...

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

dquirion78 (1/21/2014)
thanks !
I just read about money and decimal (19,4) Nobody seems to have the same answer about what the best data type...

It is probably best to avoid money data types also because they can also cause issues with calculations.

Use float for scientific, trigonometric, or other math function calculations.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

dquirion78 (1/21/2014)
thanks !
I just read about money and decimal (19,4) Nobody seems to have the same answer about what the best data type...

That is because there isn't a "best" datatype. The best choice is to use the one most appropriate for your situation. I generally avoid the approximate datatypes.

You might want to look at the documentation about datatypes.

http://msdn.microsoft.com/en-us/library/ms187752.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dquirion78 Ten Centuries Points: 1240 More actions · Answer 7

Rumours are that MONEY will be deprecated in a future version or incompatible with other db migration.

So l'll probably use decimal and

decimal (19,5) instead of (19,4) for exchange rate...