float decimal problem

homer.favenir · #1 (**permalink**) 03-09-09, 03:42

hi,
i cant figure out why my field `AmountFigure` is keep on rounding off
i have a value of 52206.25
and when i insert it in my table.AmountFigure it became 52206.3!
am i missing anything here?

please kindly help..
thanks

r937 · #2 (**permalink**) 03-09-09, 05:24

Quote:

Originally Posted by homer.favenir

am i missing anything here?

yes, you've forgotten to give us all the facts about the column

homer.favenir · #3 (**permalink**) 03-09-09, 06:15

Code:

CREATE TABLE  `mafee_db`.`banktransact_tbl` (
  `idbank_transact` int(10) unsigned NOT NULL auto_increment,
  `Date` date NOT NULL default '0000-00-00',
  `AccountNumber` varchar(45) NOT NULL default '',
  `AccountName` varchar(100) NOT NULL default '',
  `Currency` varchar(45) NOT NULL default '',
  `AmountWords` text NOT NULL,
  `TransactType` varchar(45) NOT NULL default '',
  `TransactBy` varchar(45) NOT NULL default '',
  `PreparedBy` varchar(45) NOT NULL default '',
  `Bank` varchar(100) NOT NULL default '',
  `Branch` varchar(45) NOT NULL default '',
  `Note` text,
  `AmountFigure` float NOT NULL default '0',
  PRIMARY KEY  (`idbank_transact`),
  KEY `Index_2` (`Date`,`AccountNumber`,`TransactType`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

the field is `AmountFigures` it suppose to accept Float,
but when i insert 52206.25, it became 52206.3

thanks

healdem · #4 (**permalink**) 03-09-09, 06:25

I would have thought for a n umneric, especially a monetary value you would be using a fixed precision value such as decimal(size,NoDecimals). although you cna use a FLOAT/DOUBLE in MySQL using and sepcify a size and precision of value

MySQL :: MySQL 5.0 Reference Manual :: 10.2 Numeric Types

persoanlly I would expect to see soemhtign such as decimal(16,4), that would allow 12 places to the left of the decimal and 4 to the right). you oculd vary that to meet your requirements (ie if you know you will only ever need 2 decimal places and you are only going to have say 5 to the left (ie range -9,999.99 to 99,999.99 then you could specify decimal (7,2). but for most currency values you'd be better off establishing your maximum size and at least doubling it to allow for rounding and other errors (unless of course you are writing stuff for Zimbabwe* where you'd need to use a column as big as physically possible to allow for the rampant inflation)

*an other countries such as the UK and our soon to be inflation busting 'quantative easing' AKA printing money

r937 · #5 (**permalink**) 03-09-09, 08:09

homer, try this and please show me what results you get --

Code:

CREATE TABLE  test_homer (
  `idbank_transact` int(10) unsigned NOT NULL auto_increment,
  `AmountFigure` float NOT NULL default '0',
  PRIMARY KEY  (`idbank_transact`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO test_homer (`AmountFigure`) VALUES
 ( 9.37 )
,( 9.037 )
,( 9.0037 )
,( 9.00037 )
,( 9.000037 )
,( 9.0000037 )
,(      93.7 )
,(     930.7 )
,(    9300.7 )
,(   93000.7 )
,(  930000.7 )
,( 9300000.7 )
;
SELECT * FROM test_homer 
;

please see if you can offer an explanation for what's happening

see if the phrase "number of significant digits" might mean anything in this context

now realize that every number you insert into a FLOAT column is converted first to scientific notation --

x.xxxxx * 10^^(+/-nnn)

the number x.xxxxx will have only 1 digit to the left of the decimal point, and the number nnn will indicate the power of 10 to be applied

the important point here is that x.xxxxx has only a certain number of significant digits

you did not specify how many, so it used the default

any idea what that default might be?

homer.favenir · #6 (**permalink**) 03-09-09, 22:07

@r937
this is exactly what i need.
but im wondering, what is the difference between my table and your table?
do i have to drop my previous table and make a new one?
thanks

r937 · #7 (**permalink**) 03-09-09, 22:13

i think you can simply modify the table

try this (untested) --

ALTER TABLE mafee_db.banktransact_tbl
CHANGE COLUMN AmountFigure DECIMAL(9,2) NOT NULL DEFAULT 0.0

make sure you choose an adequate scale and precision for your DECIMAL

homer.favenir · #8 (**permalink**) 03-09-09, 22:31

however it doesnt work
error:

Code:

ALTER TABLE mafee_db.banktransact_tbl 
CHANGE COLUMN AmountFigure DECIMAL(9,2) NOT NULL DEFAULT 0.0

Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECIMAL(9,2) NOT NULL DEFAULT 0.0' at line 2

and also the

Code:

CREATE TABLE  test_homer (
  `idbank_transact` int(10) unsigned NOT NULL auto_increment,
  `AmountFigure` float NOT NULL default '0',
  PRIMARY KEY  (`idbank_transact`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO test_homer (`AmountFigure`) VALUES
 ( 9.37 )
,( 9.037 )
,( 9.0037 )
,( 9.00037 )
,( 9.000037 )
,( 9.0000037 )
,(      93.7 )
,(     930.7 )
,(    9300.7 )
,(   93000.7 )
,(  930000.7 )
,( 9300000.7 )
;
SELECT * FROM test_homer 
;

when i insert the value 52206.75 in 'AmountFigure' it became 52206.8

r937 · #9 (**permalink**) 03-09-09, 22:48

Quote:

Originally Posted by homer.favenir

however it doesnt work

you need to look it up in the manual yourself

i think the syntax requires the old column name and then the new column name

but ~you~ should be looking it up and testing it

Code:

ALTER TABLE mafee_db.banktransact_tbl 
CHANGE COLUMN AmountFigure 
AmountFigure DECIMAL(9,2) NOT NULL DEFAULT 0.0

Quote:

Originally Posted by homer.favenir

when i insert the value 52206.75 in 'AmountFigure' it became 52206.8

yes, that's right

that's why i asked you to do it

homer.favenir · #10 (**permalink**) 03-09-09, 23:22

Quote:

Originally Posted by r937

you need to look it up in the manual yourself

i think the syntax requires the old column name and then the new column name

but ~you~ should be looking it up and testing it

Code:

ALTER TABLE mafee_db.banktransact_tbl 
CHANGE COLUMN AmountFigure 
AmountFigure DECIMAL(9,2) NOT NULL DEFAULT 0.0

yes, that's right

that's why i asked you to do it

DECIMAL(9,2) doesnt work also
it rounds off my 'AmountFigures' in one decimal place.
i need this 'AmountFigures' to be round off in three decimal places.

please help

thanks

r937 · #11 (**permalink**) 03-09-09, 23:25

Quote:

Originally Posted by homer.favenir

i need this 'AmountFigures' to be round off in three decimal places.

OH FOR CRYING OUT LOUD!!!

did you not understand what i meant when i said that you should choose an adequate scale and precision for your DECIMAL

obviously, the "2" in DECIMAL(9,2) is inadequate, yes?

i will not answer any more in this thread for a minimum of 24 hours, to give you a chance to test these changes for yourself

homer.favenir · #12 (**permalink**) 03-09-09, 23:49

ok, i got it. thanks rudy...

mike_bike_kite · #13 (**permalink**) 03-10-09, 05:04

Quote:

Kill Nothing But Time;

How apt

Internet Services

Web Development

Digital Marketing

Consumer Tech