Round function

Round:

It Rounds the given expression into the specified length. It can also truncates the expression to the specified length.

Syntax:

ROUND ( numeric_expression , length [ ,function ] )

  • Numeric_Expression: It can be expression which returns a numeric value or a simple numeric value. We can also use a column name of a table which is of numeric type.
  • Length: It is a numeric value which sets the precision to the output (Rounded value). It must be an expression or a numeric value of type TinyInt, SmallInt or Int.
  • Function: It is the type of operation to be performed. Its value is 0 by default. Then the expression will be rounded. If it is other than 0, the expression will be Truncated. This is optional.

The ROUND() function rounds the expression to up or down.

Rounding Up –  Rounding to the next number is called as Rounding Up.

Rounding Down – Rounding to the previous number is called as Rounding Down.

Return Types:

Round-return-types

Before looking at few examples on ROUND(), we will see few facts of it through which we will get to know how ROUND() works.

As we know that, we can have positive or negative number for length in the syntax.

  • When it is Positive number, the given expression is rounded to the number of decimal positions specified in length.
  • When it is Negative number, the given expression is rounded on the left part of the decimal point in length.
  • If we use the third parameter, the output will be truncated instead of rounding.

EXs:

————————————————————————————-
— ROUND() with Integers.
————————————————————————————-
— Testing ROUND() with a Positive Integer.
————————————————————————————-
DECLARE @Round_PInt_Test INT = 100

SELECT ROUND(@Round_PInt_Test, 0) — Returns 100
SELECT ROUND(@Round_PInt_Test, -1) — Returns 100
SELECT ROUND(@Round_PInt_Test, 1) — Returns 100
SELECT ROUND(@Round_PInt_Test, -2) — Returns 100
SELECT ROUND(@Round_PInt_Test, 2) — Returns 100
SELECT ROUND(@Round_PInt_Test, -3) — Returns 0
SELECT ROUND(@Round_PInt_Test, 3) — Returns 100
————————————————————————————-
— Testing ROUND() with a Negative Integer.
————————————————————————————-
DECLARE @Round_NInt_Test INT = -100

SELECT ROUND(@Round_NInt_Test, 0) — Returns -100
SELECT ROUND(@Round_NInt_Test, -1) — Returns -100
SELECT ROUND(@Round_NInt_Test, 1) — Returns -100
SELECT ROUND(@Round_NInt_Test, -2) — Returns -100
SELECT ROUND(@Round_NInt_Test, 2) — Returns -100
SELECT ROUND(@Round_NInt_Test, -3) — Returns 0
SELECT ROUND(@Round_NInt_Test, 3) — Returns -100
————————————————————————————-
— ROUND() with decimals.
————————————————————————————-
— Testing ROUND() with a Positive Decimal number.
————————————————————————————-
DECLARE @Round_PDec_Test DECIMAL(10,3) = 123.45

SELECT ROUND(@Round_PDec_Test, 0) — Returns 123.000
SELECT ROUND(@Round_PDec_Test, -1) — Returns 120.000
SELECT ROUND(@Round_PDec_Test, 1) — Returns 123.500
SELECT ROUND(@Round_PDec_Test, -2) — Returns 100.000
SELECT ROUND(@Round_PDec_Test, 2) — Returns 123.450
SELECT ROUND(@Round_PDec_Test, -3) — Returns 0.000
SELECT ROUND(@Round_PDec_Test, 3) — Returns 123.450
————————————————————————————-
— Testing ROUND() with a Negative Decimal number.
————————————————————————————-
DECLARE @Round_NDec_Test DECIMAL(10,3) = -123.45

SELECT ROUND(@Round_NDec_Test, 0) — Returns -123.000
SELECT ROUND(@Round_NDec_Test, -1) — Returns -120.000
SELECT ROUND(@Round_NDec_Test, 1) — Returns -123.500
SELECT ROUND(@Round_NDec_Test, -2) — Returns -100.000
SELECT ROUND(@Round_NDec_Test, 2) — Returns -123.450
SELECT ROUND(@Round_NDec_Test, -3) — Returns 0.000
SELECT ROUND(@Round_NDec_Test, 3) — Returns -123.450
————————————————————————————-
— Testing Rounding Up
————————————————————————————-
SELECT ROUND(7,-1) — Returns 10
SELECT ROUND(7,-2) — Returns 0

SELECT ROUND(678, -1) — Returns 680
SELECT ROUND(678, -2) — Returns 700
SELECT ROUND(678, -3) — Returns 1000
SELECT ROUND(678, -4) — Returns 0

SELECT ROUND(-234, -1) — Returns -230
SELECT ROUND(-234, -2) — Returns -200
SELECT ROUND(-234, -3) — Returns 0
SELECT ROUND(-234, -4) — Returns 0
————————————————————————————-
— Testing Rounding Down
————————————————————————————-
SELECT ROUND(4,-1) — Returns 0
SELECT ROUND(4,-2) — Returns 0

SELECT ROUND(234, -1) — Returns 230
SELECT ROUND(234, -2) — Returns 200
SELECT ROUND(234, -3) — Returns 0
SELECT ROUND(234, -4) — Returns 0

SELECT ROUND(-678, -1) — Returns -680
SELECT ROUND(-678, -2) — Returns -700
SELECT ROUND(-678, -3) — Returns -1000
SELECT ROUND(-678, -4) — Returns 0
————————————————————————————-
— Understanding the use of 3rd parameter(Truncate)
————————————————————————————-
— Few examples without third parameter(By default 0).
————————————————————————————-
SELECT ROUND(335.71534657, 2) — Returns 335.72000000
SELECT ROUND(335.71534657, 3) — Returns 335.71500000
SELECT ROUND(335.71534657, 4) — Returns 335.71530000
SELECT ROUND(335.71534657, 5) — Returns 335.71535000
SELECT ROUND(335.71534657, 6) — Returns 335.71534700
SELECT ROUND(335.71534657, 7) — Returns 335.71534660
————————————————————————————-
— Above examples with the third parameter.
————————————————————————————-
SELECT ROUND(335.71534657, 2, 1) — Returns 335.71000000
SELECT ROUND(335.71534657, 3, 2) — Returns 335.71500000
SELECT ROUND(335.71534657, 4, 3) — Returns 335.71530000
SELECT ROUND(335.71534657, 5, 4) — Returns 335.71534000
SELECT ROUND(335.71534657, 6, 5) — Returns 335.71534600
SELECT ROUND(335.71534657, 7, 6) — Returns 335.71534650
————————————————————————————- 

Happy Reading  :-)

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *