How to round a number in Sql Server

If you want to start learning how Sql server works, Rounding numbers in Sql is a good startpoint.

1. Rounding in Math

Rounding in Sql is mainly based on rounding math concept. This consist in going to a near value making the rounded value easier but less exact. For rounding you can find two principal rules .

The first one is that if the number you are rounding is followed by 6, 7, 8, 9 round the number up (this means that you have to add one to the rounding digit). The second one means that if the number you want to round is followed by 0, 1, 2, 3 or 4 round the number down (keep the rounding digit and drop all digits located on the right of it).

Some examples are.

0.46147 = 0.3514 = 0.351 = 0.35 = 0.4 = 0

3.62171 = 3.6217 = 3.622 = 3.62 = 3.6 = 4

The other rule is that when the first digit dropped is five make the preceding digit even.

0.54519 = 0.5452 = 0.545 = 0.54 = 0.5

1.25195 = 1.2519 = 1.252 = 1.25 = 1.2 = 1

62.5347 = 62.535 = 62.54 = 62.5 = 62

Round is useful when handling numeric data; there are some reasons to round a number, like formatting before insert values into a table or displaying values formatted.

First, only datatypes that contains decimal part can be round (decimal, money, etc.) so if you try to round an integer number it is going to return the same result.

2. Demo Data

For this example we are going to create a table and populate it with random values.

/*Creating table */

CREATE TABLE numbers(
	int_number  int NULL
	,decimal_number  decimal(18,4) NULL
	,money_number  money NULL
	,float_number  float NULL
)
/*Generating random numbers and inserting them into the table*/

INSERT INTO numbers VALUES((RAND()*(1-10)+10)
	                   ,RAND()*(1-10)+10
			   ,RAND()*(1-10)+10
			   ,RAND()*(1-10)+10)

)

3. Rounding in Sql Server

There are multiple forms to round a number.

We are going to use a simple round function to compare its effects on different data types.

Round function rounds a number to a establish number of decimals.

SELECT 
          int_number as int_number
	 ,ROUND(int_number,2) as round_int
	 ,decimal_number
	 ,ROUND(decimal_number,2) as round_decimal 
	 ,money_number
	 ,ROUND(money_number,2) as round_money
	 ,float_number
	 ,ROUND(float_number,2) as round_float  
FROM numbers

As you can see int_number is not affected by round, decimal number is rounded by 2 but its decimal part of 4 is kept with “00” on the right site, money and float numbers are affected and rounded to two decimal numbers.

By applying Floor function to our numbers we can see the following results.
Floor It ignores the decimal part of a number and returns only the Integer part of that number (Round Down).

SELECT 
	  int_number
	 ,FLOOR(int_number) as floor_int
	 ,decimal_number
	 ,FLOOR(decimal_number) as floor_decimal 
	 ,money_number
	 ,FLOOR(money_number) as floor_money
	 ,float_number
	 ,FLOOR(float_number) as floor_float  
FROM numbers

We can notice that floor_decimal number format is completely ignored and decimal part is completely rounded, float number kept 2 decimal numbers and float_number is completely rounded.

By applying Ceiling function to our numbers we can see the following results.
Ceiling if decimal part of the number is not 0 it round the number to the upper number (Round Up)

SELECT 
	  int_number
	 ,CEILING(int_number) as ceiling_int
	 ,decimal_number
	 ,CEILING(decimal_number) as ceiling_decimal
	 ,money_number 
	 ,CEILING(money_number) as ceiling_money
	 ,float_number
	 ,CEILING(float_number) as ceiling_float  
FROM numbers

Floor pattern is repeated on the numbers.

As you can see rounding a number on Sql Server is easy and results are predictable.