Msg#: 4073685 posted 6:32 pm on Feb 3, 2010 (gmt 0)
I've got a field called "shipping" that's set as a FLOAT at 5,2. Due to an error in my PHP (not rounding a calculation), I found that INSERTS were being saved as 2.9800000035 and somehow that amount is even being saved in the database (shouldn't it have been dropped to 2 places with the 5,2?)
Now I'm trying to fix the problem. I temporarily changed the structure to 20,17 and can clearly see the stray numbers. In phpmyadmin I issued:
SELECT shipping from order WHERE shipping > 2.98 AND shipping < 2.99;
and get tons of results. So then I thought it would be simple to fix. I did:
UPDATE order SET shipping = '2.98' WHERE shipping > 2.98 AND shipping < 2.99;
and I get no changes. Why? And how to fix the entries in the database?
Msg#: 4073685 posted 7:08 pm on Feb 3, 2010 (gmt 0)
What you should really use there, for ease of access and reliability, is the decimal type.
update order change shipping shipping decimal(12,2) not null default '0.00';