List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:November 20 2006 5:04pm
Subject:RE: float numbers
View as plain text  
You will see the same rounding problems if you alter a FLOAT to either a
DOUBLE or a DECIMAL. Unless all of your original data had integer values,
it's too late unless you force rounding yourself.

For example, if you alter the FLOAT field to DECIMAL(23,3), add .005 to each
number, and then alter the field to DECIMAL(22,2) you will wind up with a
value that is exactly what you want. (This applies only to positive numbers,
for negative numbers you need to decide if you want to round up towards 0 or
down away from 0.)

Using DOUBLE will not solve the problem, it will merely make it less likely
to cause trouble.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Ahmad Al-Twaijiry [mailto:ahmadt@stripped]
> Sent: Friday, November 17, 2006 5:34 PM
> To: mos; Dan Nelson
> Cc: mysql@stripped
> Subject: Re: float numbers
>
> Thanks Dan & Mos
>
> I have many tables that use float (in production database), if I
> convert all of the float column to DOUBLE or DECIMAL (using alter ),
> is there any impact or anything I should know that could happen to me
> ?
>
> is there any page that describe the differences between FLOAT, DECIMAL
> and DOUBLE in MySQL 5.0.* ?
>
> Thanks
>
> On 11/18/06, mos <mos99@stripped> wrote:
> > At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote:
> > >Ho everyone
> > >
> > >I have a column in a table defined as float
> > >
> > >mynumber float(20,2)
> > >
> > >if we say mynumber column in a row is 1000000 ,  when I
> run this SQL :
> > >
> > >UPDATE Table SET mynumber=mynumber-100.15
> > >
> > >the mynumber column will be 999900 not 999899.85
> > >
> > >what is the problem ?
> >
> > Ho Ho Ho,
> >
> > Float only uses 4 bytes and does not have much precision so
> you'll get
> > rounding. Try DOUBLE instead.
> >
> > Mike
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
> --
> echo "Hello World :)"
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>



Thread
float numbersAhmad Al-Twaijiry17 Nov
  • Re: float numbersDan Nelson17 Nov
    • Re: float numbersChris W18 Nov
  • Re: float numbersmos17 Nov
    • Re: float numbersAhmad Al-Twaijiry17 Nov
      • Re: float numbersDan Nelson17 Nov
      • Re: float numbersAsif Lodhi19 Nov
        • Re: float numbersAhmad Al-Twaijiry20 Nov
      • RE: float numbersJerry Schwartz20 Nov
  • ANN: SQL Maestro 6.12 releasedSQL Maestro Group14 Dec