By Callum SQL Server has a built-in function to round numbers up or down. Learn how to use this function to round up or round down numbers in SQL Server.
Introduction
If you’re working with a relational database, you’ll have access to a number of built-in functions that can help extend the capabilities of SQL. In this article, we’ll take a look at how the ROUND() function works using some practical examples.
What Is the SQL ROUND() Function?
The ROUND function in SQL is useful to round a given number to the nearest integer or to a certain decimal place.
The round function accepts three parameters:
ROUND(number, decimals, operation)
number: Specify the number on which you want to apply round-off function.
decimals: It specifies the number of decimal places up to which the specified number is to be rounded.
operation: It is an optional parameter with a default value 0. In the default value, the ROUND function rounds the result to the number of decimal places.
With a value other than 0, the ROUND function truncates the result to the number of decimal places.
Let’s understand the round function with examples.
SELECT ROUND(365.216, 2);
Result 365.220
SELECT ROUND(125.3154, 2);
Result: 125.3200
SELECT ROUND(123.467, 0);
Result: 123.000
SELECT ROUND (569.128, 0) AS Round0,
ROUND (569.128, 1) AS Round1,
ROUND (569.128, 2) AS Round2
Result:
Round0: 569.000
Round1:569.100
Round2:569.130
SELECT ROUND (578.696, 0) AS RoundZero, ROUND (578.696, -1) AS RoundNegative1, ROUND (578.696, -2) AS RoundNegative2
Result:
Round0:579.000
Round1:580.000
Round2:600.000
SELECT ROUND (578.696, -4)
Result: 0.000
SELECT ROUND(-23.456, 2, 1);
Result: -23.450
SELECT round(-456.789, -1);
Result: -460.000
SELECT round(4560, -3, 3);
Result: 4000
SELECT ROUND(5.153745,0)
Result: 5.000000
DECLARE @i float
SET @i = 698.5745
SELECT ROUND(@i, 1)AS [Result 1],ROUND(@i, 2)AS [Result 2],ROUND(@i, 3)AS [Result 3],
ROUND(@i, -1)AS [Result 4],ROUND(@i, -2)AS [Result 5],ROUND(@i, -3)AS [Result 6]
,ROUND(@i, 0, 1)AS [Result 7],ROUND(@i, 0, 0)AS [Result 8]