Level 18
When I tried changin' data type straight from float to double i got my price field turn from 29978.1 to 29978.1287356928375
Feb 1, 2021
5
Level 18
MYSQL: convert float field to double
Hi, I need to convert float field to double field, how can I safely do that without corrupting my data?
Is it safe to convert float field to varchar(255) then convert it to double?
Here is my table definition
CREATE TABLE `orders` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`price` FLOAT NOT NULL,
);
I know that using double or float is not a good idea for prices, but I can afford only double or float now. Thanks!
Level 39
in 2 times
add price2 double;
that update ... set price2=cast(price as double);
than delete price & rename price2 to price if you want.
1 like
Level 1
Sorry, you can't. The DOUBLE and FLOAT data types represent approximate numeric data values. They are stored in such a way that they can contain a wide range of numbers (from very big to very small) but at the cost of being a bit inaccurate. See Floating-point types for more information.
Here is the link:
1 like
Level 73
I know you already have a large database with prices but if you can you should use regular numbers to store monetary values. For example in US currency you would store it in cents. So if something costs $19.95 you should store it as 1995. So in that case I would do something like
CREATE TABLE temp_table AS (SELECT field1, field2 , price * 100 AS price FROM table);
1 like
Level 18
Converted float field to varchar(255) then converted it to double. And eveerythink was fine. Thanks to everyone!
Please or to participate in this conversation.