ROUND( Numeric, Scale)
Rounds the number given to the specified number of decimal places.
If a non-numeric is passed to ROUND an exception is thrown. If a float type is passed into Round an estimated result may occur due to reasons outlined below.
There are some built in rounding strangeness around floating point values in general (do you remember the comp sci class that talks about float being an estimation of the number?). But if you start rounding numbers that are estimates you can end up with strange results.
If you need to round a data value do not use float. Float is one of the types that is an estimate. This is from the MSDN Docs on float.
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
You can still do a rounding operation on a float column, but you need to cast it first in order to prevent prevision loss.
select round(2.562,2) + round(2.577,2); -- Gives the result 5.1400000000000006 due to math rounding of floats select CAST(round(2.562,2) as decimal) + cast(round(2.577,2) as decimal); -- Gives correct result because of 128 bit math select round(CAST(2.562 as DECIMAL),2) + round(CAST(2.577 AS DECIMAL),2); -- Gives correct result because of 128 bit math
Sometimes you just want a different round operation than .Net uses in general. Some VB users are used to the classic VB 6 rounding, some developers only want the rounding for banking, etc. You can implement a custom rounding function to meet your need. No matter what type of rounding you decide internally, make sure you use the DECIMAL type for the input. That is the highest precision type in .Net. Decimals are 128 bit values, where floats are only 64 bit.
CREATE FUNCTION [MyRound] (@Operand Decimal,@Places Int) RETURNS DECIMAL AS BEGIN DECLARE @x decimal; DECLARE @i int; set @x = @Operand * power(10,@Places); set @i = @x; set @x = @i + iif((@x - @i) >= .5,1,0); set @x = @x / power(10,@Places); RETURN(@x); END
This creates a function that implements a custom round based upon the POWER and the places argument.
select MYROUND( 2.569, 2 ); -- 2.57 select MYROUND( 2.5649, 2 ); -- 2.56
You can then use it in your own application like the above.
SELECT ROUND(123.9994, 3), ROUND(123.9995, 3); --RETURNS: 123.999 124 SELECT ROUND( 123.99994, 0); --RETURNS: 124 SELECT ROUND(748.58, -1); -- 750 SELECT ROUND(748.58, -2); -- 700 SELECT ROUND(748.58, -3); -- 1000 (Crashes in SQL Server 2008)