How about your CFQUERYPARAM tag? Are you using the right type? On 9/30/07, Rick Faircloth <[EMAIL PROTECTED]> wrote: > 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 > > > > > > > > > > > > > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289819 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
