Hi, Barney... and thanks for the reply. I think I have my table set up as you describe.
Here's the table information: -- 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; It's the "debit_amount" column that is of concern here... Ideas? Rick -----Original Message----- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Sunday, September 30, 2007 11:44 AM To: CF-Talk Subject: Re: Trouble storing monetary values in MySQL 5 If you're losing all the digits after the decimal point, you've got your column defined incorrectly. Both 4.1 and 5.0 handle DECIMAL columns, including arbitrary numbers of fractional digits without issue. I'd guess that there was some subtle change in the handling between 4.1->5.0, and that's the source of the problem, since I know it works just find on both versions. Here's a column def I use with MySQL 5: hourlyRate decimal(10,2) not null That says use 2 fractional digits and 8 (10 - 2) integral digits. cheers, barneyb On 9/30/07, Rick Faircloth <[EMAIL PROTECTED]> wrote: > Hi, all. > > > > This does actually involve CF 8, (and yes, I'm on CF 8 now, from 4.5 > > with my new VPS from ahphosting.com!) since I manipulate the value > > (typically, MySQL 4.1) before inserting. > > > > I've got a MySQL field of decimal for the monetary value. > > > > Previously, in MySQL 4.1, I would use decimal as the field type. > > > > Upon inserting I would use lsparsecurrency(fieldname) and > > insert. Worked fine MySQL 4.1. > > > > But 5.0 drops the digits after the decimal upon storage. > > I'm looking for the right combination of SQL and CF code to > > properly format, store, and retrieve the monetary value. > > > > Clues anyone? > > > > Rick > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289818 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
