MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:October 1 2007 1:47pm
Subject:Re: Having trouble storing monetary values...
View as plain text  
Hi Rick,

(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?

Baron

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
> CURRENT_TIMESTAMP,
>   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.
> 
> Ideas?
> 
> Rick
> 
> -----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.
> 
> Baron
> 

-- 
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/
Thread
Having trouble storing monetary values...Rick Faircloth30 Sep
  • Re: Having trouble storing monetary values...Baron Schwartz30 Sep
Re: Having trouble storing monetary values...Baron Schwartz1 Oct
  • RE: Having trouble storing monetary values...Rick Faircloth1 Oct