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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 FLOOR, CEILING (or CEIL), ROUND, ABS all available. ROUND uses half-away-from-zero. TRUNCATE(n, d) truncates without rounding.
PostgreSQL ✓ Supported 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 ✓ Supported 2000 FLOOR, CEILING, ROUND, ABS available. ROUND(n, d, 1) with a third argument of 1 truncates instead of rounding. No TRUNC function.
Oracle ✓ Supported 7 FLOOR, CEIL, ROUND, ABS available. TRUNC(n, d) truncates. ROUND uses half-away-from-zero.
SQLite ✓ Supported 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: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 2000 Oracle: Since 7 SQLite: Since 3.0.0

Per-Database Syntax & Notes

MySQL

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

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

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

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

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);