Menu
When casting 1 to decimal in MySQL, I was expecting 1.0000, but instead the output is 0.9999.
What exactly is happening here?
SELECT CAST(1 AS DECIMAL(4,4))
328
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
To cast VARCHAR to INT, we can use the cast() function from MySQL. Here is the syntax of cast() function. For our example, we will create a table with the help of create command. Display all records with the help of select statement.
MySQL is clipping the converted value to the largest one that will fit in the target type. A decimal(4, 4) doesn't allot any significant digits to the left of the decimal place.
This cast does not cause overflow only when the server is not running a strict mode as described in the documentation.
97
out of range
For example, DECIMAL(5,2) store any value with five digits and two decimals, its range from -999.99 to 999.99.
SELECT CAST(1 AS DECIMAL(5,4)) -> 1.0000
SELECT CAST(1 AS DECIMAL(4,3)) -> 1.000
SELECT CAST(0.0001 AS DECIMAL(4,4)) -> 0.0001
SELECT CAST(0.00001 AS DECIMAL(4,4)) -> 0.0000
SELECT CAST(12345 AS DECIMAL(5,4)) -> 9.9999
More info:
https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
