How to round down to nearest integer in MySQL?

How would I round down to the nearest integer in MySQL?

Example: 12345.7344 rounds to 12345

mysql's round() function rounds up.

I don't know how long the values nor the decimal places will be, could be 10 digits with 4 decimal places, could be 2 digits with 7 decimal places.

Use FLOOR:

SELECT FLOOR(your_field) FROM your_table

Use FLOOR().

It will to round your decimal to the lower integer. Examples:

SELECT FLOOR(1.9) /* return 1 */
SELECT FLOOR(1.1) /* return 1 */

If you want to round your decimal to the nearest integer, use ROUND(). Examples:

SELECT ROUND(1.9) /* return 2 */
SELECT ROUND(1.1) /* return 1 */

If you want to round your decimal to the upper integer, use CEILING(). Examples:

SELECT CEILING(1.9) /* return 2 */
SELECT CEILING(1.1) /* return 2 */

SELECT FLOOR(12345.7344);

Read more here.

SUBSTR will be better than FLOOR in some cases because FLOOR has a "bug" as follow:

SELECT 25 * 9.54 + 0.5 -> 239.00

SELECT FLOOR(25 * 9.54 + 0.5) -> 238  (oops!)

SELECT SUBSTR((25*9.54+0.5),1,LOCATE('.',(25*9.54+0.5)) - 1) -> 239

It can be done in the following two ways:

The 2nd-way explanation: Assume 12345.7344 integer. So, 12345.7344 - 0.5 = 12345.2344 and rounding off the result will be 12345.

Related