(I'm CCing the list in again -- keeping the discussion on-list lets
everyone see it and potentially solve their own problems by searching
archives in future).
It looks to me like it ought to be working. What is the exact INSERT
INTO statement that isn't working?
Are there any triggers on the table?
Rick Faircloth wrote:
> Hi, Baron, and thanks for the reply.
> Here's the table info I got from Navicat:
> -- Table "debits" DDL
> CREATE TABLE `debits` (
> `debit_id` int(11) NOT NULL auto_increment,
> `debit_category_id` int(11) NOT NULL default '0',
> `debit_name` varchar(50) default NULL,
> `debit_amount` decimal(10,2) unsigned NOT NULL,
> `debit_recipient` varchar(50) default NULL,
> `debit_description` text,
> `personal` tinyint(1) default NULL,
> `debit_date` date default NULL,
> `debit_time` time default NULL,
> `debit_location` varchar(30) default NULL,
> `contact_person` varchar(20) default NULL,
> `contact_phone` varchar(20) default NULL,
> `contact_email` varchar(50) default NULL,
> `date_entered` timestamp NULL default '0000-00-00 00:00:00' on update
> PRIMARY KEY (`debit_id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
> The "debit_amount" is the field I'm having problems with.
> I've been using MySQL 4.1 for a couple of years and haven't
> had any problems. But after upgrading to 5.0.45-community-nt,
> my way of handling monetary value isn't working the way I desire.
> When the data is inserted in the db, it's being rounded up or down
> to the near whole number. 5.75 becomes 6 or 5.25 becomes 5.
> No decimal to be found.
> -----Original Message-----
> From: Baron Schwartz [mailto:baron@stripped]
> Sent: Sunday, September 30, 2007 11:03 AM
> To: Rick Faircloth
> Cc: mysql@stripped
> Subject: Re: Having trouble storing monetary values...
> Rick Faircloth wrote:
>> Hi, all.
>> I'm having trouble storing monetary values.
>> When MySQL 5.0 stores the entered value of 5.23,
>> it storing it as 5, dropping off the values after the decimal.
>> The field is the decimal type with 2 specified as the number
>> of values to store after the decimal.
>> Can anyone provide some clues on how to set up the field
>> or any other changes I need to make to properly store
>> the monetary values?
> Please send us the exact column definition from SHOW CREATE TABLE. You
> should be able to use a definition like DECIMAL(10,2) or similar.