How to round down to nearest integer in MySQL?
MysqlSqlRoundingMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
Use FLOOR:
SELECT FLOOR(your_field) FROM your_table
Solution 2 - Mysql
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 */
Solution 3 - Mysql
SELECT FLOOR(12345.7344);
Read more here.
Solution 4 - Mysql
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
Solution 5 - Mysql
The FLOOR() function will return the largest integer value that is smaller than or equal to a number.
example :
SELECT FLOOR(columnName) FROM tableName;
Solution 6 - Mysql
It can be done in the following two ways:
select floor(desired_field_value) from tableselect 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.
Solution 7 - Mysql
Try this,
SELECT SUBSTR(12345.7344,1,LOCATE('.', 12345.7344) - 1)
or
SELECT FLOOR(12345.7344)
SQLFiddle Demo
Solution 8 - Mysql
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
Solution 9 - Mysql
Both Query is used for round down the nearest integer in MySQL
- SELECT FLOOR(445.6) ;
- SELECT NULL(222.456);