CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
tidge
tidge

Reputation: 447

SQL Rounding/Truncation

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

Upvotes: 1

Views: 319

Answers (1)

M.Ali
M.Ali

Reputation: 69584

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.

Upvotes: 1

Related Questions