In SQL, what do ROUND, TRUNC, CEIL and FLOOR each return for 2.7 and for -2.7, and why?
answer
- one direction rule per function
- negatives separate the four
- toward zero versus downward
- TRUNC is not spelled the same everywhere
basics
~10 sROUND goes to the nearest integer (3, -3), TRUNC drops the fraction toward zero (2, -2), CEIL moves up toward positive infinity (3, -2), and FLOOR moves down toward negative infinity (2, -3).
solid answer
~30 sEach function has one direction rule, and negative values are where the rules separate. `ROUND` goes to the nearest value, `TRUNC` goes toward zero, `CEIL` goes up to the next integer and `FLOOR` goes down, so `CEIL(-2.7)` is -2 and `FLOOR(-2.7)` is -3. `ROUND` and `TRUNC` usually take a digit count too: `ROUND(2.567, 1)` is 2.6 and `TRUNC(2.567, 1)` is 2.5. The names are widely available but not universal: MySQL writes `TRUNCATE(x, d)`, and SQL Server truncates with `ROUND(x, d, 1)`, names its ceiling function `CEILING`, and requires the digit argument on `ROUND`.
code
sql · 5 linesSELECT round(-2.7), trunc(-2.7), ceil(-2.7), floor(-2.7);
-- -3 | -2 | -2 | -3
SELECT round(42.4382, 2), trunc(42.4382, 2);
-- 42.44 | 42.43go deeper
Recall the four directions and state the -2.7 results without hesitating: -3, -2, -2, -3. Say toward zero for TRUNC and down for FLOOR rather than describing both as removing decimals.
Explain why TRUNC agrees with FLOOR on positives and with CEIL on negatives, and show where it matters by bucketing negative readings. Mention that the digit argument belongs to ROUND and TRUNC.
Show the habit of checking the argument first: a correct CEIL over an already truncated integer quotient is still wrong. Call out the spelling differences before a query moves between engines.
Treat rounding direction as a visible choice in shared query code. When a team targets more than one engine, name the truncation and ceiling spellings in its conventions so a port cannot quietly change results.
## One direction rule per function All four functions turn a number with a fraction into a simpler number. Each follows one fixed rule about which way to move along the number line, and that rule does not change with the sign of the input. What changes is how the rule looks to someone thinking in terms of "bigger" and "smaller". | Function | Rule | `2.7` | `-2.7` | |---|---|---|---| | `ROUND(x)` | nearest integer | 3 | -3 | | `TRUNC(x)` | drop the fraction, moving toward zero | 2 | -2 | | `CEIL(x)` | smallest integer not below `x` | 3 | -2 | | `FLOOR(x)` | largest integer not above `x` | 2 | -3 | Two pairs are worth memorising, because each pair agrees on one side of zero and disagrees on the other: - **Positive input:** `TRUNC` and `FLOOR` agree, which is why people assume they are the same function. - **Negative input:** `TRUNC` and `CEIL` agree, because moving toward zero is moving upward. - **Any input that is already whole:** all four return it unchanged. ## The digit argument `ROUND` and `TRUNC` commonly accept a second argument, the number of decimal places to keep. `CEIL` and `FLOOR` generally do not, and they always land on a whole number. - `ROUND(42.4382, 2)` is 42.44 and `TRUNC(42.4382, 2)` is 42.43. - A negative digit count works to the left of the point on most engines: PostgreSQL returns 1230 for `ROUND(1234.56, -1)`. - SQLite treats a negative digit count as zero, so the same call returns 1235.0 there. - To take a ceiling at two decimals, scale by hand: `CEIL(x * 100) / 100`, on a value that is not an integer type. On PostgreSQL the two-argument forms of `round` and `trunc` are defined for `numeric` only, so a `double precision` value has to be cast first. ## Spelling is not portable These names are widely available, but do not assume any one of them works on every engine. | Engine | Truncate | Ceiling | `ROUND` with one argument | |---|---|---|---| | PostgreSQL | `TRUNC(x)` or `TRUNC(x, d)` | `CEIL` or `CEILING` | allowed | | MySQL | `TRUNCATE(x, d)`, `d` required | `CEIL` or `CEILING` | allowed | | SQL Server | `ROUND(x, d, 1)` | `CEILING` only | error, `d` required | | Oracle | `TRUNC(x)` or `TRUNC(x, d)` | `CEIL` only | allowed | | SQLite | `trunc(x)` | `ceil` or `ceiling` | allowed | SQLite's `trunc`, `ceil` and `floor` exist only in builds with its math functions compiled in, so check before relying on them. `ROUND` and `FLOOR` are the two names these five share. ## Where the difference changes a result Two everyday queries depend on picking the right function. 1. **Bucketing values that can be negative.** To group readings into bands ten wide, `FLOOR(reading / 10.0) * 10` sends -3 to the band starting at -10 and 3 to the band starting at 0. Using `TRUNC` sends both to 0, so that one band is twice as wide as the others and the chart is silently wrong. 2. **Counting pages.** Rows divided by page size must round up: `CEIL(25 / 10.0)` is 3. Written as `CEIL(25 / 10)` on an engine that divides integers, the division happens first and yields 2, and `CEIL(2)` is still 2. The function was right and its input was already truncated. The second case is the reason to read an expression from the inside out: `ROUND`, `TRUNC`, `CEIL` and `FLOOR` only act on the value they receive. ## Return type and NULL - All four return `NULL` for a `NULL` input, like most scalar functions. - The result is often a numeric type rather than an integer type. PostgreSQL's `ceil(numeric)` returns `numeric`, so a value that prints as `3` is not an `integer`. - Display can differ too: SQL Server keeps the input's scale and shows `ROUND(2.567, 1)` as 2.600, where PostgreSQL shows 2.6. ## Common wrong predictions - Expecting `CEIL(-2.7)` to be -3, by treating "ceiling" as "further from zero". - Expecting `FLOOR(-2.7)` to be -2, by treating "floor" as "remove the decimals". - Using a `CAST` to an integer type as if it were `TRUNC`. Engines differ: PostgreSQL rounds 2.7 to 3 on the cast, while SQL Server and SQLite truncate it to 2. What `ROUND` does at an exact half, such as 2.5, is a separate question, because it depends on whether the value has an exact or an approximate type.
- Why does CEIL(25 / 10) return 2 rather than 3 on an engine that divides integers?The argument is evaluated first: `25 / 10` is integer division and yields 2, so `CEIL` receives a whole number and returns it. Make the division exact, `CEIL(25 / 10.0)`, and the result is 3. Integer arithmetic also works: `(25 + 10 - 1) / 10`, when both values are positive integers.
- Does CAST(2.7 AS INTEGER) behave like ROUND or like TRUNC?It depends on the engine. PostgreSQL rounds on that cast and returns 3, while SQL Server and SQLite truncate and return 2. A query that needs one specific behaviour should call `ROUND` or the engine's truncation function explicitly and cast afterwards, so the choice is visible and survives a move between engines.
- How do you put readings into bands ten wide when some readings are negative?Use `FLOOR(reading / 10.0) * 10`. `FLOOR` moves every value down, so -3 lands in the band starting at -10 and every band has the same width. `TRUNC` moves toward zero, so -3 and 3 share a band starting at 0, which is then twice as wide as its neighbours.
Picture a building with basement levels, where -2.7 sits between level -2 and level -3. The ceiling above you is -2 and the floor beneath you is -3, TRUNC rides toward ground level to -2, and ROUND steps to the nearest level, -3.
- CEIL always moves a number further from zero, so CEIL(-2.7) is -3.
- FLOOR just removes the decimals, so FLOOR(-2.7) is -2.
- TRUNC and FLOOR are the same function under two names.
- TRUNC is spelled the same way on every SQL engine.
- ROUND with one argument is accepted on every SQL engine.