VistaDB 6
VistaDB / Developer's Guide / SQL Reference / Functions / Mathematical Functions / ROUND
In This Topic
    ROUND
    In This Topic
    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.

    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.

    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.

    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