Developers deal with numerous data types on a day- to-day basis. We need to change the data type or format as per the user requirement. We use ‘SQL Server rounding function’ like SQL **Round, Ceiling and Floor **to round the values to the nearest numbers. We perform an arithmetic calculation on data as well. It is a challenging task to change the value of a number to an approximate number. We do not want to display decimal numbers in the application front end.

The output of the aforementioned round functions depends upon the data types as well. Let’s have a look at each SQL Server Rounding functions definitions in this article.

## SQL Server Rounding function – Round()

In SQL Server, Round function round a number to a specified length or precision.

**The SQL Round function accepts three parameters as per the following syntax:**

ROUND ( numeric_expression , length [ ,function ] )

**Numeric_expression**: It is an exact number or numeric data type expression. We cannot use a bit of data type in this parameter**Length:**It is the number of decimal places to which we want to round the number. We can use both positive and negative values in this. We can use only**tinyint**,**smallint**, or**int**data types in this parameter**Function:**It is an optional parameter and specifies the truncation point of the value. The default value for this parameter is zero. If you do not specify any values (default value), it rounds the numeric_expression. If the value is other than zero, it truncates the numeric_expression

## SQL Server Rounding function – CEILING()

We use the SQL CEILING function to evaluate the value and return the smallest integer greater than, or equal to, the specified numeric expression. It only accepts one value.

**Syntax of SQL CEILING function:**

CEILING ( numeric_expression )

**Numeric_expression**: It is an exact number or numeric data type expression. We cannot use a bit of data type in this parameter

## SQL Server Rounding function – FLOOR()

The SQL Floor function is similar to a CEILING function with one difference. It returns the largest smallest integer greater than, or equal to, the specified numeric expression. It also accepts one value.

**Syntax of SQL FLOOR function:**

FLOOR ( numeric_expression )

**Numeric_expression**: It is an exact number or numeric data type expression. We cannot use a bit of data type in this parameter

Let’s walk through SQL Server Rounding functions with examples in the next section.

### Example 1: SQL Server Rounding functions with Integer data type

In this example, we define a variable to hold integer value and use SQL Server Rounding functions to view the output.

#### Positive Integer value

1 2 3 4 5 6 7 8 | DECLARE @value INT; SET @value = 10; SELECT ROUND(@value, 0); SELECT CEILING(@value); SELECT FLOOR(@value); |

In the output, we can see that all three SQL Rounding functions (Round, CEILING and Floor) return the same output for the positive integer value.

We do not have any decimal digit; therefore, Round function does not round the value. Similarly, CEILING and FLOOR function also do not return the smallest or largest integer value.

#### Negative Integer value

Let’s use negative integer value in the previous example and see the difference in the output.

1 2 3 4 5 | DECLARE @value INT; SET @value = -10; SELECT ROUND(@value, 2); SELECT CEILING(@value); SELECT FLOOR(@value); |

We also get similar output for the negative integer value as well.

#### Positive Integer Value with Positive or Negative Length

In the round function, we can use both positive and negative numbers for the second parameter **Length.** Let’s use different values in the Length parameter for the round function.

1 2 3 4 5 6 | SELECT ROUND(8, 0); SELECT ROUND(8, 1); SELECT ROUND(8, 2); SELECT ROUND(8, -1); SELECT ROUND(8, -2); SELECT ROUND(8, -3); |

In the output, we can see the following.

- For positive integer with
**Positive**length, it always returns the same number without any rounding - For positive integer with
**Negative**length, it rounds the number to the nearest tens place - For positive integer with
**Negative**length but length is larger than the number of digits before the decimal point, ROUND always returns 0

Let’s look at another example of three digit positive integer number. It rounds the value until negative length 3 to the nearest tens place. If we use negative length 4 with a three-digit number, it returns 0.

1 2 3 4 5 6 | SELECT ROUND(888, 0); SELECT ROUND(888, 1); SELECT ROUND(888, 2); SELECT ROUND(888, -1); SELECT ROUND(888, -2); SELECT ROUND(888, -3); |

Let’s use another example with integer value 457.

1 2 3 4 | SELECT ROUND(457, -1); SELECT ROUND(457, -2); SELECT ROUND(457, -3); SELECT ROUND(457, -4); |

In this example, we can note the following regards to nearest tens place.

- SELECT ROUND(457, -1) rounds down the value to 460
- SELECT ROUND(457, -2); rounds up the value to 500

#### Negative Integer Value with Negative Length

In this example, let’s use a negative length integer value along with the negative length as well. It also rounds up and down the value as per the length.

1 2 3 4 | SELECT ROUND(-888, -1); SELECT ROUND(-888, -2); SELECT ROUND(-888, -3); SELECT ROUND(-888, -4); |

### Example 2: SQL Rounding functions with decimal data type

In this example, let’s use a variable with decimal data type and check the output for different length precision.

#### Decimal data type value with positive Length

In the following query, use the positive length for the decimal data type.

1 2 3 4 5 | DECLARE @value DECIMAL(10, 2); SET @value = 12.07; SELECT ROUND(@value, 1); SELECT ROUND(@value, 2); SELECT ROUND(@value, 3); |

In this example, we can see that with decimal values round up to the nearest value as per the length.

#### Decimal data type value with negative Length

In the following query, use the negative length for the decimal data type.

1 2 3 4 5 | DECLARE @value DECIMAL(10, 2); SET @value = 12.07; SELECT ROUND(@value, -1) SELECT ROUND(@value, -2) SELECT ROUND(@value, -3) |

We can see that the output is rounded to nearest value .If length is insufficient, it also return zero value.

#### Decimal data type value with CEILING and Floor SQL Server rounding functions

1 2 3 4 | DECLARE @value DECIMAL(10, 2); SET @value = 12.07; SELECT CEILING(@value) SELECT FLOOR(@value) |

### Example 3: SQL Rounding functions with float data type

#### Float data type value with positive and negative Length

Let’s use the float data type with positive and negative length values.

1 2 3 4 5 6 7 8 | DECLARE @value float(10) SET @value = 11.23456 SELECT ROUND(@value, -1) SELECT ROUND(@value, -2) SELECT ROUND(@value, 1); SELECT ROUND(@value, 2); SELECT ROUND(@value, 3); SELECT ROUND(@value, 4); |

In this example, you can see the following things.

- For Positive length 1, we get the output 11.2 because the next digit is 3 that is less than 5
- For Positive length 2, we get the output 11.23 because the next digit is 4 that is less than 5
- For Positive length 3, we get the output 11.235 because the next digit is 5 that is equals to 5
- For Positive length 4, we get the output 11.2346 because the next digit is 6 that is greater than 5

We can also understand using the following example.

1 2 3 4 5 6 | DECLARE @value float(10) SET @value = .92719 SELECT ROUND(@value, 1); SELECT ROUND(@value, 2); SELECT ROUND(@value, 3); SELECT ROUND(@value, 4); |

- For Positive length 1, we get the output 0.9 because the next digit is 2 that is less than 5
- For Positive length 2, we get the output 0.93 because the next digit is 7 that is greater than 5
- For Positive length 3, we get the output 0.927 because the next digit is 1 that that is less than 5
- For Positive length 4, we get the output 0.9272 because the next digit is 9 that is greater than 5

#### Float data type value with CEILING and Floor SQL Server rounding functions

1 2 3 4 | DECLARE @value float(10); SET @value = 0.92719; SELECT CEILING(@value) SELECT FLOOR(@value) |

### Example 4: SQL Rounding functions with Numeric data type

#### Numeric data type value with positive and negative Length

In this example, we will use a numeric data type with both the positive and negative length values. It follows the same behavior as of decimal data type.

1 2 3 4 5 6 7 8 | DECLARE @value numeric(10,5) SET @value = .92831 SELECT ROUND(@value, 1); SELECT ROUND(@value, 2); SELECT ROUND(@value, 3); SELECT ROUND(@value, 4); SELECT ROUND(@value, -1); SELECT ROUND(@value, -2); |

- For Positive length 1, we get the output 0.90000 because the next digit is 2 that is less than 5
- For Positive length 2, we get the output 0.93000 because the next digit is 8 that is greater than 5
- For Positive length 3, we get the output 0.92800 because the next digit is 3 that is less than 5
- For Positive length 4, we get the output 0.92830 because the next digit is 1 that is less than 5
For negative length, we get the output 0.00000

#### Numeric data type value with CEILING and Floor SQL Server rounding functions

1 2 3 4 | DECLARE @value numeric(10,5) SET @value = .92831 SELECT CEILING(@value) SELECT FLOOR(@value) |

### Example 5: SQL Server rounding function Truncation using the third argument

In the above examples, we have not used the third argument **Function to **truncate the result or round the result. By default, it uses value 0 to round the result. If we want to truncate the result, we can specify a value other than 0.

1 2 3 4 | SELECT ROUND(7.4567, 2) 'Rounded (by default)', ROUND(7.4567, 2, 0) 'Rounded with default function value', ROUND(7.4567, 2, 1) 'Rounded with explicit function value' |

## Conclusion

In this article, we explored the SQL Server Rounding functions with different data types. I would suggest reviewing them as per your environment. If you have any comments or questions, feel free to leave them in the comments below.

### Rajendra Gupta

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.

He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

#### Latest posts by Rajendra Gupta (see all)

- The Decomposition Tree in Power BI Desktop - January 24, 2020
- Estimating AWS RDS SQL Server costs - January 23, 2020
- Auditing in AWS RDS SQL Server - January 20, 2020