SQL Rounding/Truncation

Question

Something seems off with the SQL ROUND function. I need to truncate some numbers at the second decimal place. This is very straightforward using the ROUND function described here:

https://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx

I haven't done extensive testing. But I have tried this on SQL Server 2008 R2 and 2012. It seems to work great for almost every number I have tried except for 2.8 It always returns 2.79. 2.9 returns 2.9, 2.4 returns 2.4. But 2.8 is off. This is when I use a REAL.
If I change the data type to FLOAT then 2.4 returns 2.39 and 2.9 returns 2.89. I have no idea why. What am I missing?

See sample code below.
Putting on asbestos underwear as I'm sure I'm missing something obvious

DECLARE @x AS REAL

SELECT @x = 2.8

SELECT ROUND(@x,2,1) -- 2.79 WHAT?

SELECT @x = 2.8145

SELECT ROUND(@x,2,1) -- 2.81 OK

SELECT @x = 2.4

SELECT ROUND(@x,2,1) -- 2.4 OK

SELECT @x = 2.9

SELECT ROUND(@x,2,1) -- 2.9 OK

SELECT @x = 2.23954

SELECT ROUND(@x,2,1) -- 2.23 OK

That is the nature of floating point math. If you want precision and scale use numeric or decimal and define precision and scale. https://technet.microsoft.com/en-US/library/ms187912(v=SQL.105).aspx Float is approximate which works fine some of the time but not if you need precision and scale. Prior post on SO about this-->http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server Simply put... use the right data type and problem goes away. — xQbert, Aug 28 '15 at 17:43

score 1 · Accepted Answer · answered Aug 28 '15 at 17:51

1

DECLARE @x AS REAL, @y AS DECIMAL(10,2)

SELECT @x = 2.8 ,  @y = 2.8

SELECT ROUND(@x,2,1) [Real] , ROUND(@y,2,1) [Decimal]

Result:

Real    Decimal
2.79    2.80

The reason is the initial data types for variables, Real or Float are considered approximate data types , usually used to stored the imaginary number like Mass of earth , Distance to Mercury etc.

When using Float or Real data type(approximate data types) sql server sees 2.8 and 2.79 almost/approximately the same value hence the difference.

If you use the exact data types like Numeric or Decimal the precision is also taken into account hence the expected results.

answered Aug 28 '15 at 17:51

M.Ali

67,945
13
101
127

you're not getting the desired result using `2.23954` unless the desired result is `2.24` – JamieD77 Aug 28 '15 at 17:58
No the expected result would be `2.23` because you are truncating anything after two decimal places. – M.Ali Aug 28 '15 at 18:03
in your example just by setting `@y = 2.23954` converts it to 2.24.. the `Round(@y,2,1)` has no effect and still outputs 2.24 – JamieD77 Aug 28 '15 at 18:05
1

Just need to bump the scale of the DECIMAL up to 6 and it will truncate to 2.23 correctly. – tidge Aug 28 '15 at 18:06
1

well, 2.230000. But I can handle the rest from there. – tidge Aug 28 '15 at 18:07
at least you taught us not to always trust `ROUND(numberic_expression, length, [function])` :) – JamieD77 Aug 28 '15 at 18:09
@JamieD77 as I have mentioned in my answer with detail, when dealing with Decimal or Numeric data types precision has a very important role to play, as Tidge has already mentioned when you are assigning more decimal places to a variable you would need to extend the precision of your variable too, to get a precise answer hence the precision. – M.Ali Aug 28 '15 at 18:10
@JamieD77 no you can trust the function but always consider the data type too :) – M.Ali Aug 28 '15 at 18:11
extend the precision by 4 places more than you need?.. i dont see that in your answer at all – JamieD77 Aug 28 '15 at 18:14
If tidge understands then that's all that matters – JamieD77 Aug 28 '15 at 18:15

SQL Rounding/Truncation

1 Answers1