top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

SQL Server: Difference between floor, ceiling and round

0 votes
1,272 views

In SQL queries sometimes we need to round off some decimal  or float values, at that time we always think that which option to be applied while we have three different kinds of system defined SQL rounding functions-Ceiling, Floor and Round.

 

CEILING

Get the value on the right side of the decimal and returns the smallest integer greater or equal to, the specified values.

 

FLOOR

Get the value on the right side of the decimal and returns the largest integer less or equal to the specified values (only number)

 

ROUND

Rounds a positive or negative value to a specific length.

 

Example of SQL rounding functions i.e. floor, ceiling and round

 

 

Difference between Ceiling, Floor and Round in SQL Server

posted Aug 9, 2017 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

SQL Server: CEILING Function

This SQL Server tutorial explains how to use the CEILING function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value that is greater than or equal to a number.

Syntax

The syntax for the CEILING function in SQL Server (Transact-SQL) is:

CEILING( number )

Parameters or Arguments

number

The number used to find the smallest integer value.

Note

  • See also the FLOOR and ROUND functions.

Applies To

The CEILING function can be used in the following versions of SQL Server (Transact-SQL):

  • SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server CEILING function examples and explore how to use the CEILING function in SQL Server (Transact-SQL).

For example:

SELECT CEILING(32.65);
Result: 33

SELECT CEILING(32.1);
Result: 33

SELECT CEILING(32);
Result: 32

SELECT CEILING(-32.65);
Result: -32

SELECT CEILING(-32);
Result: -32
 READ MORE
SQL Server: FLOOR Function

This SQL Server tutorial explains how to use the FLOOR function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the FLOOR function returns the largest integer value that is equal to or less than a number.

Syntax

The syntax for the FLOOR function in SQL Server (Transact-SQL) is:

FLOOR( number )

Parameters or Arguments

number

The value used to determine the largest integer value that is equal to or less than a number.

Note

  • See also the CEILING and ROUND functions.

Applies To

The FLOOR function can be used in the following versions of SQL Server (Transact-SQL):

  • SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server FLOOR function examples and explore how to use the FLOOR function in SQL Server (Transact-SQL).

For example:

SELECT FLOOR(5.9);
Result: 5

SELECT FLOOR(34.29);
Result: 34

SELECT FLOOR(-5.9);
Result: -6
 READ MORE
SQL Server: ROUND Function

This SQL Server tutorial explains how to use the ROUND function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the ROUND function returns a number rounded to a certain number of decimal places.

Syntax

The syntax for the ROUND function in SQL Server (Transact-SQL) is:

ROUND( number, decimal_places [, operation ] )

Parameters or Arguments

number

The number to round.

decimal_places

The number of decimal places rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.

operation

Optional. The operation can be either 0 or any other numeric value. When it is 0 (or this parameter is omitted), the ROUND function will round the result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the result to the number of decimal_places.

Note

  • If the operation parameter is 0 (or not provided), the ROUND function will round the result to the number of decimal_places.
  • If the operation parameter is non-zero, the ROUND function will truncate the result to the number of decimal_places.
  • See also the CEILING and FLOOR functions.

Applies To

The ROUND function can be used in the following versions of SQL Server (Transact-SQL):

  • SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

Let's look at some SQL Server ROUND function examples and explore how to use the ROUND function in SQL Server (Transact-SQL).

For example:

SELECT ROUND(125.315, 2);
Result: 125.320    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, 2, 0);
Result: 125.320    (result is rounded because 3rd parameter is 0)

SELECT ROUND(125.315, 2, 1);
Result: 125.310    (result is truncated because 3rd parameter is non-zero)

SELECT ROUND(125.315, 1);
Result: 125.300    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, 0);
Result: 125.000    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, -1);
Result: 130.000    (result is rounded because 3rd parameter is omitted)

SELECT ROUND(125.315, -2);
Result: 100.000    (result is rounded because 3rd parameter is omitted)
 READ MORE
...