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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use FLOOR:

SELECT FLOOR(your_field) FROM your_table

Method 2

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 */

Other useful rounding

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 */

Method 3

SELECT FLOOR(12345.7344);

Read more here.

Method 4

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

Method 5

The FLOOR() function will return the largest integer value that is smaller than or equal to a number.

example :
SELECT FLOOR(columnName) FROM tableName;

Method 6

It can be done in the following two ways:

  • select floor(desired_field_value) from table
  • select round(desired_field_value-0.5) from table

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

Method 7

Try this,

SELECT SUBSTR(12345.7344,1,LOCATE('.', 12345.7344) - 1)

or

SELECT FLOOR(12345.7344)

SQLFiddle Demo

Method 8

if you need decimals can use this

DECLARE @Num NUMERIC(18, 7) = 19.1471985
SELECT FLOOR(@Num * 10000) / 10000

Output: 19.147100
Clear: 985
Add: 00

OR use this:

SELECT SUBSTRING(CONVERT(VARCHAR, @Num), 1, CHARINDEX('.', @Num) + 4)

Output: 19.1471
Clear: 985

Method 9

Both Query is used for round down the nearest integer in MySQL

  1. SELECT FLOOR(445.6) ;
  2. SELECT NULL(222.456);


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x