1

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
tidge
  • 437
  • 2
  • 7
  • 16
  • Consider rounding in your application rather than in SQL. – Brad Peabody Aug 28 '15 at 17:40
  • 3
    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

1 Answers1

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.

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