The FLOOR() function in SQLite returns the largest integer value that is less than or equal to a given numeric expression. Essentially, it “rounds down” a decimal number to the nearest integer below it.

This function is useful in cases where you want to discard the decimal portion of a number without rounding up.

Syntax

The syntax goes like this:

FLOOR(X)

Where X is the numeric expression to be “floored”.

Example

Suppose you want to find the floor of several decimal numbers:

SELECT FLOOR(5.9) AS floor_result1, 
       FLOOR(-2.7) AS floor_result2, 
       FLOOR(3.0) AS floor_result3;

Result:

+---------------+---------------+---------------+
| floor_result1 | floor_result2 | floor_result3 |
+---------------+---------------+---------------+
| 5.0           | -3.0          | 3.0           |
+---------------+---------------+---------------+

Explanation:

• FLOOR(5.9) returns 5 because 5 is the largest integer less than or equal to 5.9.
• FLOOR(-2.7) returns -3 because -3 is the largest integer less than or equal to -2.7.
• FLOOR(3.0) returns 3 because the number is already an integer.

Compared to ROUND()

The FLOOR() function is different to the ROUND() function. The ROUND() function would round the number up in some instances and down in others. The FLOOR() function, on the other hand, always returns the largest integer value not greater than its argument.

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789);

Result:

+---------------+---------------+
| FLOOR(3.6789) | ROUND(3.6789) |
+---------------+---------------+
| 3.0           | 4.0           |
+---------------+---------------+

Also, ROUND() allows us to specify the number of decimal places to round to:

SELECT 
    FLOOR(3.6789),
    ROUND(3.6789, 2);

Result:

+---------------+------------------+
| FLOOR(3.6789) | ROUND(3.6789, 2) |
+---------------+------------------+
| 3.0           | 3.68             |
+---------------+------------------+

Missing Arguments

Calling FLOOR() without an argument results in an error:

SELECT FLOOR();

Result:

Parse error: wrong number of arguments to function FLOOR()
  SELECT FLOOR();
         ^--- error here

Getting the Ceiling

SQLite also has a CEIL() / CEILING() function that does the opposite – returns the smallest integer that is greater than or equal to a given number.