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.

# SQL Server Differences

There are no cases in .Net to handle rounding the same way as SQL Server. SQL Server behaves differently depending upon what the type is (rounding is not the same across all types). We are relying on the Math.Round() functions to round the data for us.

# Floats are estimated types

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.

SQL Float Page on MSDN

# Casting a FLOAT value

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

# Implementing a custom round function

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.

# Remarks

## More Information

What Every Computer Scientist Should Know About Floating-Point Arithmetic

Calculate Precision and Scale in Dot Net

## Example

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)