I am going to keep going with my series of short articles or tidbits on Transaction SQL math functions. Most, if not all, of these functions have been in the product before the release of SQL Server 2005.
I am very proud of my daughter making the advance math class in sixth grade. I am dedicating these blogs to her hard class work and love of the subject.
I will be exploring the ROUND() function today. This function takes as input a expression in either exact numeric or approximate numeric form. This does not include the BIT data type. It returns a numeric value rounded to the specified length or precision.
The example below tests the ROUND() function with three different inputs: negative numbers, positive numbers and a null value.
|
1 2 3 4 5 6 7 8 9 10 |
-- -- The ROUND Function -- -- Round number to correct length SELECT ROUND(10.45, 1) AS TENTHS, ROUND(121.45, -1) AS NEAREST10, ROUND(NULL, -1) AS UNKNOWN; GO |
The output from the test is listed below.
|
1 2 3 4 5 |
output: TENTHS NEAREST10 UNKNOWN ----------- ----------- ----------- 10.50 120.00 NULL |
This function returns some interesting results if the maximum length is passed. We end up with return value of either a 0 or 1000 depending on how close the number xxx.xx is to the mid point. Since 1000 can no be stored in decimal(5,2) data type, a arithmetic overflow is generated. See the below example.
|
1 2 3 4 5 6 7 8 9 10 |
-- -- Incorrect length -- -- Returns zero SELECT ROUND(499.99, -3) AS ZERO; -- Errors with overflow SELECT ROUND(500.00, -3) AS OVERFLOW; </span> |
The output from the example is listed below.
|
1 2 3 4 5 6 7 8 9 10 |
output: ZERO --------- 0.00 OVERFLOW --------- Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type numeric. |
The overflow be avoided by casting the number to a real before rounding!
To recap, this function will return the numeric expression rounded to the specified length or precision.
