What is FLOOR and ceiling function in SQL?

The main difference between CEIL and FLOOR in SQL is that CEIL helps to obtain the smallest integer value that is bigger than or equal to a number while FLOOR helps to obtain the largest integer value that is smaller than or equal to a number.

Nội dung chính Show

Key Areas Covered
What is Floor in SQL
Difference Between CEIL and FLOOR in SQL
About the Author: Lithmee
You May Also Like These
SQL Server Rounding function – CEILING()
SQL Server Rounding function – FLOOR()
Example 1: SQL Server Rounding functions with Integer data type
Example 2: SQL Rounding functions with decimal data type
Example 3: SQL Rounding functions with float data type
Example 4: SQL Rounding functions with Numeric data type
Example 5: SQL Server rounding function Truncation using the third argument
What is floor function in SQL?
What does ceiling mean in SQL?
Why do we use floor and ceiling functions?
What is floor function?

A Database Management System (DBMS) is a software that helps to store and manage data in databases. A DBMS that follows the relational model is a Relational DBMS (RDBMS). There are various RDBMS such as MySQL. The programmer can execute a Structured Query Language (SQL) to perform operations on the data in the databases. There are several SQL functions, and CEIL and FLOOR are two of them.

Key Areas Covered

1. What is CEIL in SQL
– Definition, Functionality
2. What is FLOOR in SQL
– Definition, Functionality
3. Difference Between CEIL and FLOOR in SQL
– Comparison of Key Differences

Key Terms

CEIL, FLOOR, RDBMS, SQL

The CEIL() function helps to return the smallest integer value that is bigger than or equal to a specific number.

What is FLOOR and ceiling function in SQL?

The syntax is as follows.

CEIL (number)

An example is as follows.

SELECT CEIL(50.87);

It returns 51 as the output.

Another example is as follows.

SELECT CEIL (60.12);

It returns 61 as the output.

What is Floor in SQL

The FLOOR () function helps to return the largest integer value that is smaller than or equal to a specific number. The syntax is as follows.

FLOOR (number)

An example is as follows.

SELECT FLOOR (50.87);

It returns 50 as the output.

Another example is as follows.

SELECT FLOOR (60.12);

It returns 60 as the output.

Difference Between CEIL and FLOOR in SQL

Definition

CEIL() is a function that returns the smallest integer value that is bigger than or equal to a number while FLOOR() is a function that returns the largest integer value that is smaller than or equal to a number. Thus, this is the main difference between CEIL and FLOOR in SQL.

Usage

Another difference between CEIL and FLOOR in SQL is that CEIL() helps to obtain the smallest integer value that is bigger than or equal to a number. FLOOR() helps to obtain the largest integer value that is smaller than or equal to a number.

Conclusion

RDBMS such as MySQL allows the programmers to write SQL queries to perform operations on the data stored in the databases. Two such functions are CEIL and FLOOR. The main difference between CEIL and FLOOR in SQL is that CEIL() helps to obtain the smallest integer value that is bigger than or equal to a number while FLOOR() helps to obtain the largest integer value that is smaller than or equal to a number.

Reference

1. “MySQL CEIL() Function”, w3schools.com, Available here.
2. “MySQL FLOOR() Function”, w3schools.com, Available here.

Image Courtesy:

1. “2394312” (CC0) via Pixabay

About the Author: Lithmee

Lithmee holds a Bachelor of Science degree in Computer Systems Engineering and is reading for her Master’s degree in Computer Science. She is passionate about sharing her knowldge in the areas of programming, data science, and computer systems.

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

DECLARE@valueINT;

SET@value=10;

SELECTROUND(@value,0);

SELECT CEILING(@value);

SELECTFLOOR(@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.

DECLARE@valueINT;

SET@value=-10;

SELECTROUND(@value,2);

SELECT CEILING(@value);

SELECTFLOOR(@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.

SELECTROUND(8,0);

SELECTROUND(8,1);

SELECTROUND(8,2);

SELECTROUND(8,-1);

SELECTROUND(8,-2);

SELECTROUND(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.

SELECTROUND(888,0);

SELECTROUND(888,1);

SELECTROUND(888,2);

SELECTROUND(888,-1);

SELECTROUND(888,-2);

SELECTROUND(888, -3);

Let’s use another example with integer value 457.

SELECTROUND(457,-1);

SELECTROUND(457,-2);

SELECTROUND(457, -3);

SELECTROUND(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.

SELECTROUND(-888,-1);

SELECTROUND(-888,-2);

SELECT ROUND(-888,-3);

SELECTROUND(-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.

DECLARE@valueDECIMAL(10,2);

SET@value=12.07;

SELECTROUND(@value, 1);

SELECTROUND(@value,2);

SELECTROUND(@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.

DECLARE@valueDECIMAL(10,2);

SET@value=12.07;

SELECTROUND(@value, -1)

SELECTROUND(@value,-2)

SELECTROUND(@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

DECLARE@valueDECIMAL(10,2);

SET@value=12.07;

SELECTCEILING(@value)

SELECTFLOOR(@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.

DECLARE@valuefloat(10)

SET@value=11.23456

SELECTROUND(@value,-1)

SELECT ROUND(@value,-2)

SELECTROUND(@value,1);

SELECTROUND(@value, 2);

SELECTROUND(@value,3);

SELECTROUND(@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.

DECLARE@valuefloat(10)

SET@value=.92719

SELECTROUND(@value,1);

SELECT ROUND(@value,2);

SELECTROUND(@value,3);

SELECTROUND(@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

DECLARE@valuefloat(10);

SET@value=0.92719;

SELECTCEILING(@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.

DECLARE@valuenumeric(10,5)

SET@value=.92831

SELECTROUND(@value,1);

SELECTROUND(@value,2);

SELECTROUND(@value,3);

SELECTROUND(@value, 4);

SELECTROUND(@value,-1);

SELECTROUND(@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

DECLARE@valuenumeric(10,5)

SET@value=.92831

SELECTCEILING(@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.

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.

Author
Recent Posts

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at

View all posts by Rajendra Gupta

What is floor function in SQL?

The FLOOR() function returns the largest integer value that is smaller than or equal to a number.

What does ceiling mean in SQL?

The CEILING() function returns the smallest integer value that is larger than or equal to a number.

Why do we use floor and ceiling functions?

The floor and ceiling functions give us the nearest integer up or down.

What is floor function?

The Math.floor() function always rounds down and returns the largest integer less than or equal to a given number.

What is FLOOR and ceiling function in SQL?

Key Areas Covered

Key Terms

What is Floor in SQL

Difference Between CEIL and FLOOR in SQL

Definition

Usage

Conclusion

Image Courtesy:

About the Author: Lithmee

​You May Also Like These

SQL Server Rounding function – CEILING()

SQL Server Rounding function – FLOOR()

Example 1: SQL Server Rounding functions with Integer data type

Positive Integer value

Negative Integer value

Positive Integer Value with Positive or Negative Length

Negative Integer Value with Negative Length

Example 2: SQL Rounding functions with decimal data type

Decimal data type value with positive Length

Decimal data type value with negative Length

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

Example 3: SQL Rounding functions with float data type

Float data type value with positive and negative Length

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

Example 4: SQL Rounding functions with Numeric data type

Numeric data type value with positive and negative Length

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

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

Conclusion

What is floor function in SQL?

What does ceiling mean in SQL?

Why do we use floor and ceiling functions?

What is floor function?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội

You May Also Like These