Thursday, May 24, 2007

How to Round Up in Oracle SQL

Recently I needed to provide a report that had numbers rounded to the nearest thousand. I found the ROUND command but this appeared to be only rounding off decimal points to a specified number of spaces. The syntax for this is:

SELECT ROUND(number, decimals)
FROM table;

If you use 2 for the decimals your result would look like this:
SELECT ROUND(2.123,2) FROM DUAL;
Result: 2.12
SELECT ROUND(2.129,2) FROM DUAL;
Result: 2.13

However, I needed something that would round to the nearest thousandth. So I tried a negative number for the decimals like this and found it did exactly what I wanted:
SELECT ROUND(10234,-3) FROM DUAL;
Result: 10000
SELECT ROUND(10567,-3) FROM DUAL;
Result: 11000

While this is a basic Oracle SQL function, it is not clear that you can do this, hence this post.