FLOOR

Round down to the largest integer less than or equal to the input.

Category: mathReturns: INTEGERDialect: Standard

Syntax

FLOOR(expr)

Description

## Overview Returns the largest integer value less than or equal to the input. This is the mathematical floor function. For positive fractional inputs, FLOOR rounds down toward zero; for negative fractional inputs, it rounds away from zero (so FLOOR(-4.3) is -5, not -4). FLOOR is the standard primitive for integer-index derivation from continuous quantities, bucket assignment, and truncated division. ## Behavior - Accepts any numeric type. Return type follows the input type's rules. - Returns NULL if the argument is NULL. - Integer-valued inputs are returned unchanged. - For negative inputs, FLOOR rounds toward negative infinity: FLOOR(-1.5) = -2. - FLOOR(x) + 1 > x for all non-integer x, and equals x + 1 only at exact integer inputs when the engine returns DOUBLE. ## Numeric precision - For DECIMAL inputs, FLOOR is exact. - For DOUBLE inputs, binary-representation rounding can cause surprises: FLOOR(0.1 + 0.2) returns 0 (correct), but FLOOR(0.3 / 0.1) may return 2 rather than 3 because 0.3 / 0.1 is stored as 2.9999999999999996. - Large DOUBLE magnitudes (above 2^53) are returned unchanged. ## Compatibility - Conforms to the SQL standard scalar function FLOOR. - Matches typical mathematical library implementations.

Parameters

Name	Type	Description
`expr`		Specifies the numeric value to round down. Accepts any numeric type. Returns the largest integer-valued number that is less than or equal to the input.

Examples

-- Basic literal: round down a positive fraction
SELECT FLOOR(4.7);
-- Result: 4

-- Negative fractional rounds away from zero
SELECT FLOOR(-4.3);
-- Result: -5

-- Integer input unchanged
SELECT FLOOR(7.0);
-- Result: 7

-- Small negative fraction rounds to -1
SELECT FLOOR(-0.1);
-- Result: -1

-- NULL propagation
SELECT FLOOR(NULL);
-- Result: NULL

-- Column use: bucket a continuous value into integer bins
SELECT event_id, FLOOR(metric_value) AS bucket
FROM analytics.ml.metric_samples;

Pitfalls

FLOOR rounds toward negative infinity, not toward zero. FLOOR(-1.5) = -2, not -1. Use CEIL when you need round-toward-zero for negatives.
FLOOR of a DOUBLE expression like 0.3 / 0.1 can return 2 instead of the expected 3 because the DOUBLE result is 2.9999999999999996. For exact bucketing, use DECIMAL or pre-multiply to avoid division.
Return type varies by engine. Cast explicitly if you need BIGINT for an index or identifier.
FLOOR and CAST(x AS INT) are not the same for negative numbers: CAST(-1.5 AS INT) typically truncates to -1 while FLOOR(-1.5) returns -2. Use the one that matches your intended semantics.