Truncate results from SQL to integer value

Question

I have different data values (types), like 10.00 or 2.00. How can I get only its integer value, so I want to get 10 instead of 10.00 and 2 instead of 2.00?

Answer 1

You can use the FLOOR or TRUNCATE function depending on your intention:

SELECT FLOOR(1.999)        --  1
SELECT FLOOR(-1.999)       -- -2
SELECT TRUNCATE(1.999, 0)  --  1
SELECT TRUNCATE(-1.999, 0) -- -1

Answer 2

For example, you cound try to do this:

WHERE CONVERT(your_column, SIGNED INTEGER) AS num
FROM 
  table
ORDER BY 
  num;

Based on MySQL Documentation CONVERT() and CAST():

CONVERT() provides a way to convert data between different character sets. The syntax is:

CONVERT(expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.

Besides for you also could work ROUND(X), ROUND(X,D):

Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become zero.

mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+