SQL Server FLOOR() Function
In SQL Server, FLOOR() returns the greatest integer less than or equal to the numeric expression. For negative values, that can be less than truncating the fractional part toward zero: FLOOR(-3.14) is -4. See Microsoft’s FLOOR() reference.
Syntax
FLOOR(numeric_expression)
Return type
FLOOR() does not always return an int; SQL Server determines the result type from the input:
| Input type | Return type |
|---|---|
float, real |
float |
decimal(p, s) |
decimal(p, 0) |
int, smallint, tinyint |
int |
bigint |
bigint |
money, smallmoney |
money |
bit |
float |
SQL Server raises an arithmetic overflow error if the rounded result does not fit that return type. FLOOR() rounds a value; use CAST() or CONVERT() when a particular storage type is required.
Examples
Here are examples of using the FLOOR() function:
Example 1: Floor positive and negative values
SELECT FLOOR(3.14) AS positive_floor,
FLOOR(-3.14) AS negative_floor;
Result:
| positive_floor | negative_floor |
|---|---|
| 3 | -4 |
Example 2: Floor a decimal value
SELECT FLOOR(6.789) AS floor_value;
Result:
| floor_value |
|---|
| 6 |
Conclusion
FLOOR() rounds toward negative infinity and preserves a numeric return type determined by its input. For rounding to a chosen number of decimal places, see ROUND(); for an explicit type conversion, see CAST().