What is FLOOR() in SQL?

The FLOOR() function returns the next largest integer that is less than or equal to a number. Figure 1, below, shows the mathematical representation of the FLOOR() function.

Figure 1: Mathematical representation of FLOOR() function

Syntax

FLOOR(number)

Parameter

This function requires a number or numeric value as a parameter.

Return Value

The FLOOR() function returns the next largest integer that is less than or equal to the number set as a parameter.

Example

/*example showing how to use FLOOR(X)*/
-- positive number
select FLOOR(3.13);
-- negative number
select FLOOR(-2.78);

Now, consider another example in which the teacher wants to round down the marks of the students.

The figure below shows the student table, followed by the query that rounds the marks down.

Students

Student ID

Student Name

Student Marks

1

David

22.5

2

Luiss

20.14

3

Harvey

15.3

4

Lucy

25

5

Andrew

10.7

/*Query returns the marks of all the students after
applying FLOOR() */
select studentID, studentName, FLOOR(studentMarks) as studentMarks
from Students