MySQL decimal field is rounding and shouldn't be

Hi there! I have a MySQL table that includes the following table column:

	percentDegree	decimal(3,1)	Not NULL	Default 0.0

Open in new window


When I try to run an update...

UPDATE tbl_degrees SET percentDegree = '76.9' WHERE dataDate = '2015'

Open in new window


...the 76.9 is entered in the table as 77.0.

I've seen other questions like this on here but it's usually a matter of people not having and decimal places defined or using the INT type. I've also seen a comment that "you can use FLOAT, but that comes with its own problems" but can't seem to find what those problems would be. Also, isn't this exactly the data type you would use decimals for?

Thanks!
SiobhanElaraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Julian HansenConnect With a Mentor Commented:
And if you do this
<cfquery datasource="#APPLICATION.ds#" result="bloop">
UPDATE #FORM.tableName#
SET #FORM.dataName# = <cfqueryparam value="#FORM[i]#" scale="1" cfsqltype="cf_sql_decimal">
WHERE countyID = <cfqueryparam value="#countyID#" cfsqltype="cf_sql_integer">
</cfquery>

Open in new window

I don't use ColdFusion but according to the docs - the omission of the scale parameter defaults the value to 0 decimal places.
0
 
Ryan ChongCommented:
what about:

UPDATE tbl_degrees SET percentDegree = 76.9 WHERE dataDate = '2015'

?
1
 
Kim WalkerWeb Programmer/TechnicianCommented:
How are you viewing the value that's stored in the table? Are you querying the table or are you browsing the table in phpMyAdmin or a similar MySQL management utility?

If you're querying the table, it's possible that it's getting rounded somehow before it's displayed.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ryan ChongCommented:
>>the 76.9 is entered in the table as 77.0
I just did a test, both SET percentDegree = '76.9' or SET percentDegree = 76.9 are working fine. they didn't turn to 77.0

more towards a display issue for me?
0
 
Julian HansenCommented:
Confirm both '76.9' and 76.9 on a decimal(3,1) works as expected.

Where are you seeing 77.0
0
 
SiobhanElaraAuthor Commented:
It's both displaying that way and in the database that way; I'm also viewing the table through phpMyAdmin. Here's a screenshot of a query, the form, and what's in the database. (I'd entered 26.7 as seen in the query; the area under that is the result. This is a slightly different query than the original question, but all tables are having this problem.)

decimal-wonkyness.jpg
0
 
Julian HansenCommented:
And if you run this directly in PHPMyAdmin
UPDATE data_educational_attainment SET percentBachelor=26.7 WHERE countryID=0 AND dataDate='2016';

Open in new window

0
 
SiobhanElaraAuthor Commented:
That works, as does double-clicking on the cell on phpMYAdmin table view and entering it there.
0
 
Julian HansenCommented:
The problem is then in how you are submitting it to the DB - what is running that query?
0
 
SiobhanElaraAuthor Commented:
The ColdFusion code is as follows:

<cfquery datasource="#APPLICATION.ds#" result="bloop">
UPDATE #FORM.tableName#
SET #FORM.dataName# = <cfqueryparam value="#FORM[i]#" cfsqltype="cf_sql_decimal">
WHERE countyID = <cfqueryparam value="#countyID#" cfsqltype="cf_sql_integer">
</cfquery>

Open in new window


("Bloop" outputs the query shown in the screenshot above.)
0
 
SiobhanElaraAuthor Commented:
Holy crap, I hang my head in shame. Thank you for pointing out what I should have known all along!
0
 
Julian HansenCommented:
You are welcome.
0
All Courses

From novice to tech pro — start learning today.