user5313398 - 1 year ago 115

SQL Question

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

`cast(field as decimal(3,1))`

Answer Source

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)
```