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.

Bertram Gilfoyle · Accepted Answer · 2019-02-01T16:01:26.127

12

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.

edited Feb 01 '19 at 16:01

answered Jan 28 '19 at 10:27

Bertram Gilfoyle

9,899
6
42
67

1

Interesting, thanks. It doesn't seem to work with Xerial, nor should it, according to the docs: https://www.sqlite.org/lang_expr.html *"The operator % outputs the **integer value** of its left operand modulo its right operand"* – Lukas Eder Feb 01 '19 at 13:20
@LukasEder Thank you very much for the comment. There was a big mistake. It was copied from MySQL instead of SQLite. Please check the corrected version. – Bertram Gilfoyle Feb 01 '19 at 16:03
One thing to look out for is, that the number ("amount" or "x") must be of type REAL. I had a month from a database and divided it by 3 to get the quarter. Division with 2 integer operands results in an integer in SQLite. So when the month is 2, it evaluates to `0 + (0 > 0)`, so it returns 0 instead of 1. – Alien426 Dec 22 '21 at 08:57

hyperTrashPanda · Answer 2 · 2019-01-31T15:59:04.203

9

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.

edited Jan 31 '19 at 15:59

answered Jan 31 '19 at 14:45

hyperTrashPanda

838
5
18

2

Wow! I didn't think of it. But, note that It won't work always. Take for example, `CEIL(1) = 1` where `ROUND(1+0.5, 0) = 2`. It was better though, if the value would not be an integer. – Bertram Gilfoyle Jan 31 '19 at 15:35
2

Ceil and floor with real numbers ≥ 0, try 0.4999999999 instead of 0.5. – Clint Pachl Sep 05 '20 at 09:37

score 2 · Answer 3 · edited Dec 06 '22 at 09:23

See https://www.sqlite.org/lang_mathfunc.html#ceil

Apparently SQLite does provide ceiling:

ceil(X)
ceiling(X)

Return the first representable integer value greater than or equal to X. For positive values of X, this routine rounds away from zero. For negative values of X, this routine rounds toward zero.

But - sadly, it is not compiled by default - and is only active if the amalgamation is compiled using the -DSQLITE_ENABLE_MATH_FUNCTIONS compile-time option.

CEIL and FLOOR in SQLite

3 Answers3

Formulas

Examples

Linked