Back to Library

FLOOR() in SQL

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Syntax

FLOOR(number)
Return type
NUMERIC (or equivalent numeric type in the respective DBMS)

FLOOR() Function Example

SELECT FLOOR(123.45);
-- Output: 123

What is FLOOR() in SQL?

The FLOOR() function in SQL rounds a given number down to the nearest integer, always rounding toward negative infinity. This means that for positive numbers, it removes the decimal portion, and for negative numbers, it rounds further away from zero. FLOOR() is commonly used in financial calculations, statistical analysis, and mathematical operations where rounding down is necessary. It is supported across various SQL databases, including MySQL, PostgreSQL, SQL Server, and Oracle, making it a versatile function for handling numeric data.

Parameters:

  • number: The numeric value to be rounded down

Example Use Cases:

-- Calculate complete hours from minutes

SELECT FLOOR(total_minutes/60) AS complete_hours FROM time_logs; 
-- Output:
complete_hours
-------------
2
5
3
8
4

-- Get whole dollar amounts 

SELECT FLOOR(price) AS base_price FROM products; 
-- Output:
base_price
----------
19
24
99
149
499

-- Calculate complete weeks 

SELECT FLOOR(days/7) AS complete_weeks FROM periods;
-- Output:
complete_weeks
-------------
4
2
6
3
8

Notes:

  • Behavior: Always rounds down (e.g., 2.9 becomes 2, -2.1 becomes -3)

  • Performance Considerations: Basic numeric operation, highly efficient

  • Version Info: Core mathematical function available in all major DBMS

  • Deprecated/Recommended Alternatives: None

Error Handling:

  • Error: Returns NULL if input is NULL

  • Recommendation: Use COALESCE if NULL handling needed

Supported Databases:

DBMS
Function / Syntax
Example
Behavior with NULL
MySQL

FLOOR(X)

FLOOR(15.7)

Returns NULL

PostgreSQL

FLOOR(number)

FLOOR(15.7)

Returns NULL

SQL Server

FLOOR(numeric_expression)

FLOOR(15.7)

Returns NULL

SQLite

FLOOR(X)

FLOOR(15.7)

Returns NULL

BigQuery

FLOOR(X)

FLOOR(15.7)

Returns NULL

Snowflake

FLOOR(number)

FLOOR(15.7)

Returns NULL

Athena

FLOOR(number)

FLOOR(15.7)

Returns NULL

Related Functions:

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

One place for all your queries,
directly on your SQL editor