Round up or Round down in SQL Server


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]


Round up or Round down in SQL Server


16 views0 comments