CEIL and FLOOR in SQLite

Question

What is the cleanest method to find the ciel and floor of a number in SQLite? Unfortunately SQLite only has ROUND() function.

Answer 1

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 amount FROM SALES WHERE id = 128;
Floor :
SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amount FROM SALES WHERE id = 128;

I have checked all the corner cases including negative number with MySQL ceil() and floor() functions.

Answer 2

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.