user5313398 user5313398 - 5 months ago 33
SQL Question

Mysql cast to decimal give 99.9

I got a varchar field say the value as 174 but when I

cast(field as decimal(3,1))
it gives me always 99.9? How can I cast or convert it to be 17.4? Or must I change at source level the input to be input in decimal itself?

Answer

That's because 174 cannot be expressed with 2 digits before and 1 digit after the decimal point. decimal(3,1) means "3 digits in total and 1 (of the 3) after the decimal point.

mysql> select cast('174' as decimal(3,1));
+-----------------------------+
| cast('174' as decimal(3,1)) |
+-----------------------------+
|                        99.9 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

It gives a warning that can be shown with:

mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast('174' as decimal(3,1))' at row 1 |
+---------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Divide the number by 10 and try again:

mysql> select cast('174'/10 as decimal(3,1));
+--------------------------------+
| cast('174'/10 as decimal(3,1)) |
+--------------------------------+
|                           17.4 |
+--------------------------------+
1 row in set (0.00 sec)
Comments