MATH FUNCTIONS (FLOOR / CEIL / ROUND / ABS)

Functions
Buy Me A Coffee

Core numeric functions for rounding, truncating, and taking the absolute value of numeric expressions.

Basic numeric functions: FLOOR rounds down, CEIL/CEILING rounds up, ROUND rounds to a given precision, and ABS returns absolute value. Widely supported across all major databases with consistent behavior.

Compatibility

Filter by Database
SQL MATH FUNCTIONS (FLOOR / CEIL / ROUND / ABS) Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 5.0 FLOOR, CEILING (or CEIL), ROUND, ABS all available. ROUND uses half-away-from-zero. TRUNCATE(n, d) truncates without rounding.
PostgreSQL Native 7.4 FLOOR, CEIL/CEILING, ROUND, ABS all standard. ROUND on the NUMERIC type uses banker's rounding (round half to even); on float it rounds half away from zero. TRUNC() truncates.
SQL Server Native 2000 FLOOR, CEILING, ROUND, ABS available. ROUND(n, d, 1) with a third argument of 1 truncates instead of rounding. No TRUNC function.
Oracle Native 7 FLOOR, CEIL, ROUND, ABS available. TRUNC(n, d) truncates. ROUND uses half-away-from-zero.
SQLite Native 3.0.0 ROUND and ABS built-in since the beginning. FLOOR and CEIL added in 3.35.0. No TRUNC; use CAST(n AS INT) or ROUND(n - 0.5) as a workaround for positive values.

Details

The core four (FLOOR, CEIL/CEILING, ROUND, ABS) work across all five engines with only minor spelling differences. The most significant behavioral difference is ROUND: PostgreSQL uses banker's rounding (round half to even) for the NUMERIC type, meaning ROUND(2.5) = 2 and ROUND(3.5) = 4, while all other engines use half-away-from-zero (ROUND(2.5) = 3). This is a silent correctness trap when migrating financial calculations to PostgreSQL. Truncation is also inconsistent: PostgreSQL and Oracle have TRUNC(), MySQL has TRUNCATE(), SQL Server uses ROUND(n, d, 1), and SQLite requires a workaround. CEILING vs CEIL: all engines accept at least one spelling; PostgreSQL, MySQL, and SQL Server accept both.

Standard Syntax

SELECT FLOOR(4.7), CEIL(4.2), ROUND(4.567, 2), ABS(-42);

Version Support

MySQL: Native since 5.0 PostgreSQL: Native since 7.4 SQL Server: Native since 2000 Oracle: Native since 7 SQLite: Native since 3.0.0

Per-Database Syntax & Notes

MySQL Native syntax

FLOOR, CEILING (or CEIL), ROUND, ABS all available. ROUND uses half-away-from-zero. TRUNCATE(n, d) truncates without rounding.

SELECT FLOOR(4.7), CEILING(4.2), ROUND(4.565, 2), ABS(-42), TRUNCATE(4.789, 2);

PostgreSQL Native syntax

FLOOR, CEIL/CEILING, ROUND, ABS all standard. ROUND on the NUMERIC type uses banker's rounding (round half to even); on float it rounds half away from zero. TRUNC() truncates.

SELECT FLOOR(4.7), CEIL(4.2), ROUND(4.565, 2), ABS(-42), TRUNC(4.789, 2);

SQL Server Native syntax

FLOOR, CEILING, ROUND, ABS available. ROUND(n, d, 1) with a third argument of 1 truncates instead of rounding. No TRUNC function.

SELECT FLOOR(4.7), CEILING(4.2), ROUND(4.565, 2), ABS(-42); -- Truncate via ROUND third arg: SELECT ROUND(4.789, 2, 1);

Oracle Native syntax

FLOOR, CEIL, ROUND, ABS available. TRUNC(n, d) truncates. ROUND uses half-away-from-zero.

SELECT FLOOR(4.7), CEIL(4.2), ROUND(4.565, 2), ABS(-42), TRUNC(4.789, 2) FROM dual;

SQLite Native syntax

ROUND and ABS built-in since the beginning. FLOOR and CEIL added in 3.35.0. No TRUNC; use CAST(n AS INT) or ROUND(n - 0.5) as a workaround for positive values.

SELECT FLOOR(4.7), CEIL(4.2), ROUND(4.567, 2), ABS(-42);