How to round down to nearest integer in MySQL

Question

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

User · Answer

Try this   SELECT SUBSTR 12345 7344 1 LOCATE      12345 7344  - 1    or  SELECT FLOOR 12345 7344    SQLFiddle Demo

User · Answer

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

User · Answer

Use FLOOR   SELECT FLOOR your field  FROM your table

User · Answer

Use FLOOR    if you want to round your decimal to the lower integer  Examples   FLOOR 1 9    gt  1 FLOOR 1 1    gt  1   Use ROUND    if you want to round your decimal to the nearest integer  Examples   ROUND 1 9    gt  2 ROUND 1 1    gt  1   Use CEIL    if you want to round your decimal to the upper integer  Examples   CEIL 1 9    gt  2 CEIL 1 1    gt  2

User · Answer

SUBSTR will be better than FLOOR in some cases because FLOOR has a  bug  as follow   SELECT 25   9 54   0 5 - gt  239 00  SELECT FLOOR 25   9 54   0 5  - gt  238   oops    SELECT SUBSTR  25 9 54 0 5  1 LOCATE      25 9 54 0 5   - 1  - gt  239

User · Answer

SELECT FLOOR 12345 7344     Read more here

User · Answer

Both Query is used for round down the nearest integer in MySQL  SELECT FLOOR 445 6    SELECT NULL 222 456

User · Answer

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

[mysql] How to round down to nearest integer in MySQL?

SQLFiddle Demo

Examples related to mysql

Examples related to sql

Examples related to rounding