SQL 2005+

SQL Server Rounding Functions

by Richard Carr, published at http://www.blackwasp.co.uk/SQLRoundingFunctions.aspx

The fortieth part of the SQL Server Programming Fundamentals tutorial continues the discussion of mathematical functions provided by Transact-SQL (T-SQL). This article describes three functions that can be used to round floating and fixed-point numbers.

Rounding Functions

Transact-SQL provides three functions that can be used to perform rounding of numeric values. Two are used exclusively to round a value to an integer, whilst one allows rounding to a specified number of decimal places. Each of these functions works with any numeric data type.

Floor

The Floor function rounds a number downwards to the next integer value. Although the return value is an integer, it is returned as the same data type as the original value, which is provided as the only parameter. It is important, when working with negative numbers, to note that the rounding is always downwards, not towards zero.

SELECT floor(1234.56789)    -- 1234
SELECT floor(-1234.56789)   -- -1235

Ceiling

The Ceiling function is almost identical to Floor. The key difference is that the rounding is always upwards to the next integer value.

SELECT ceiling(1234.56789)  -- 1235
SELECT ceiling(-1234.56789) -- -1234

Round

The Round function is the most powerful of the three rounding options. When used with two parameters, the first argument specifies a number to be rounded. The second identifies the desired number of decimal places. The rounding algorithm uses standard mathematical rules to determine whether the result should be larger or smaller than the original value. For example, when rounding to two decimal places, if the third decimal place in the initial number is five or greater, the function rounds upwards.

SELECT round(1234.56789, 2) -- 1234.57
SELECT round(1234.56789, 0) -- 1235
SELECT round(1234.11223, 0) -- 1234

The Round function can be used with a negative number of decimal places. This permits rounding to different orders of magnitude.

SELECT round(1234.56, -2)   -- 1200
SELECT round(5678.9, -3)    -- 6000

Finally, you can add a third parameter to the Round function to determine the algorithm to be used. The default value for the third parameter is zero and indicates that the above rules are used. However, if you specify 1 for the third argument, the original value will simply be truncated.

SELECT round(1234.56789, 2, 0)  -- 1234.57
SELECT round(1234.56789, 2, 1)  -- 1235.56