VistaDB 5
ROUND

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

Float Rounding Errors

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)

See Also