Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: float decimal problem

    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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    am i missing anything here?
    yes, you've forgotten to give us all the facts about the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    @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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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
    Last edited by homer.favenir; 03-09-09 at 23:36.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    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
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    ok, i got it. thanks rudy...
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Kill Nothing But Time;
    How apt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •