The SQL ROUND function allows you to round a number to a specific number of decimal places. Let’s see how it works and see some examples.
This article applies to Oracle, SQL Server, MySQL, and Postgres.
Purpose of the SQL ROUND Function
The purpose of the SQL ROUND function is to take a number and round it to the specified number of decimal places. There are a lot of similar functions, but the ROUND function is probably the most common and it’s pretty easy to use.
ROUND can also be used with date input values. You can supply a date value to it, as well as the format, and it will round the date to that format.
SQL ROUND Syntax
The syntax for the ROUND function in Oracle, MySQL and Postgres is::
ROUND (input, decimals)In SQL Server, there is a third parameter:
ROUND (input, decimals [, operation])Let’s take a look at what these values are used for.
Parameters
The parameters of the SQL ROUND function are:
- input (mandatory): This is the value that is rounded. It can be any numeric data type or any date data type.
- decimals (optional): This determines what the input value is rounded to. If it is a number, then you specify the decimal places here. If it is a date, you specify the part of the date.
- operation (optional): This parameter in SQL Server allows you to specify either 0 or omit the value, the input is rounded. If you specify 1, the value is truncated.
This function can be used with both dates and numbers. What kind of input does it need?
ROUND with Numbers
If you are using the SQL ROUND function with numbers, then:
- input needs to be a numeric data type or a value that can be converted to a numeric data type (such as a number inside a VARCHAR value)
- decimals needs to be an integer.
- If roundto is positive, the result is rounded to the right of the decimal point
- If roundto is negative, the result is rounded to the left of the decimal point
- If roundto is 0 or omitted, the result is rounded to 0 decimal places
- If roundto is omitted, the result is in the same data type as the input value
- If roundto is specified, the result is in a NUMBER data type
- For NUMBER input values, the value is rounded away from 0 (e.g. 2.5 rounds to 3, -1.5 rounds to 2).
- For BINARY_FLOAT and BINARY_DOUBLE ∫ˆ values, the value is rounded to the nearest even number.
ROUND with Dates
If you are using the ROUND function with dates, then:
- input needs to be a date or datetime with timestamp
- roundto can be any valid format mask (e.g. year, month, day, week, minute)
- If roundto is omitted, the input is rounded to the nearest day
- The value returned is always of data type DATE, even if you specify a different datetime data type.
How Do You Always Round Up In SQL?
The ROUND function will round the number up or down, depending on the decimal places specified. It doesn’t always round up, as you can see in the examples below.
If you want to always round up in SQL, you would use the CEIL function. This function rounds a number up to the specified value, so you can use the CEIL function to round all of your values up.
You can read my guide on the CEIL function here.
How Can SQL Round Down 2 Decimal Places?
You can use the ROUND function to round to 2 decimal places, by specifying the number 2 as the second parameter. However, this will not always round down. It could round up, depending on your input value.
To always round down, you would use the FLOOR function, which is the opposite of the CEILING function mentioned above.
You can read my guide on the FLOOR function here.
How Can SQL Round To The Nearest 5?
Yes, you can do this with ROUND.
To round to the nearest 5, you use the ROUND function and divide your number by 5, then multiply it by 5.
For example:
SELECT ROUND(3162.845 / 5) * 5;Result:
3165
This function first doubles the number you wish to round, and then rounds it. Then, it halves the result. So, this example would perform these steps:
- 3162.845 / 5 =632.569
- ROUND(632.569) = 633
- 633 * 5 = 3165
The action of dividing, rounding, and then multiplying causes the result to be rounded to the nearest 5.
How Can You Round To The Nearest 100?
To round to the nearest 100, you can use a negative value for the roundto parameter in the ROUND function. The negative value will round to the left of the decimal place, so you can get a value that is rounded to the nearest 100.
SELECT ROUND(12345.67, -2);Result:
12300
See the examples below for more information.
Examples of the SQL ROUND Function
Here are some examples of the SQL ROUND function.
Example 1
This example rounds a decimal number to 1 place.
SELECT ROUND(3162.845, 1);Result:
3162.8
Example 2
This example rounds a decimal to 5 places.
SELECT ROUND(3162.8451297, 5);Result:
3162.84513
Example 3
This example rounds a decimal to -2 places.
SELECT ROUND(3162.845, -2);Result:
3200
Example 4
This example rounds a whole number to -3 places.
SELECT ROUND(142786, -3);Result:
143000
Example 5
This example rounds a decimal to 0 places.
SELECT ROUND(3162.845, 0);Result:
3163
Example 6
This example rounds a decimal with no roundto parameter specified.
SELECT ROUND(3162.845);Result:
3163
Example 7
This example rounds a number to the nearest 5.
SELECT ROUND(3162.845/5)*5;Result:
3165
Example 8
This example rounds a number to the nearest hundred.
SELECT ROUND(142786, -2);Result:
142800
Example 9
This example rounds a date to the nearest year, using Oracle’s SYSDATE function.
SELECT
SYSDATE,
ROUND(SYSDATE, 'YEAR') AS ROUNDED;Result:
| SYSDATE | ROUNDED |
| 02/APR/22 | 01/JAN/22 |
Example 10
This example rounds a date to the nearest month, using Oracle’s SYSDATE function.
SELECT
SYSDATE,
ROUND(SYSDATE, 'MONTH') AS ROUNDED;Result:
| SYSDATE | ROUNDED |
| 02/APR/22 | 01/APR/22 |
Similar Functions
Some functions which are similar to the ROUND function are:
- CEILING – This function rounds a number up to the specified precision
- FLOOR – This function rounds a number down to the specified precision
- TRUNC – This function truncates either a number or a date value to the specified precision
You can find a full list of Oracle functions here.