How do ROUND, CEIL and FLOOR differ from each other, and what do they do to negative numbers?
They all round a number, but they round it in three different directions.
The function ROUND rounds to the nearest value. The function CEIL rounds up, towards the larger number. And the function FLOOR rounds down, towards the smaller number.
-- ROUND is built into every SQLite build and takes a precision argument.
SELECT ROUND(2.5) AS round_half_up,
ROUND(-2.5) AS round_negative,
ROUND(2.345, 2) AS round_to_two_places;
round_half_up round_negative round_to_two_places
3.0 -3.0 2.35
So what do they do to negative numbers? That is where the difference shows most clearly, and the rule is that the two rounding functions and the rounding function disagree about the middle.
The function CEIL always goes towards the positive side, so CEIL(-2.5) is -2. The function FLOOR always goes towards the negative side, so FLOOR(-2.5) is -3. And the function ROUND goes away from zero, so ROUND(-2.5) is -3.
SELECT CEIL(-2.5) AS ceil_negative,
FLOOR(-2.5) AS floor_negative,
ROUND(-2.5) AS round_negative;
So here is a way to remember it. The function CEIL is the ceiling, and a ceiling is above you, so it goes up. The function FLOOR is the floor, and a floor is below you, so it goes down. And the function ROUND is not either of those, so it goes to whichever one is closer, and when the two are equally close it goes away from zero.
There is one more thing about the function ROUND that matters. You can give it a second argument, and that is the number of decimal places to keep. The function ROUND(x) with no second argument gives you a whole number, and the function ROUND(x, 2) gives you two decimal places. You should always say which one you want, because the default is not the same on every engine for every type of number.
The next question in this topic looks at why you should not use floating point numbers for money.