CEIL and FLOOR in SQLite
Formulas
Ceil : cast ( x as int ) + ( x > cast ( x as int ))
Take integer part of x and add 1 if decimal value is greater than 0
Floor : cast ( x as int ) - ( x < cast ( x as int ))
Take integer part of x and subtract 1 if decimal value is less than 0
Examples
Ceil :SELECT (cast ( amount as int ) + ( amount > cast ( amount as int ))) AS amountFROM SALES WHERE id = 128;
Floor :SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amountFROM SALES WHERE id = 128;
I have checked all the corner cases including negative number with MySQL ceil()
and floor()
functions.
You can use ROUND()
to the effect of CEIL
and FLOOR
if you add or subtract 0.5 from the number on hand. I like this more, because it's can be more readable.
Expanding on Anees' example :
Ceil : SELECT ROUND(amount+0.5, 0) AS amount FROM SALES WHERE id = 128;
Floor : SELECT ROUND(amount-0.5, 0) AS amount FROM SALES WHERE id = 128;
Thanks Anees for the comment below, I didn't think of that corner case either. His solution is more robust.