FLOOR() in SQL
Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.
Syntax
Return type
FLOOR() Function Example
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
-- Get whole dollar amounts
-- Calculate complete weeks
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:
CEIL()/CEILING()
Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.
ROUND()
Rounds a number to a specified number of decimal places or to the nearest integer.
ABS()
Returns the absolute (positive) value of a number by removing the negative sign if present.
MOD()
Returns the remainder after dividing one number by another. Also available as % operator in many databases.