Solved

MySQL decimal field is rounding and shouldn't be

Posted on 2016-09-06
12
59 Views
Last Modified: 2016-09-06
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!
0
Comment
Question by:SiobhanElara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41786176
what about:

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

?
1
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41786192
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
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41786193
>>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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 58

Expert Comment

by:Julian Hansen
ID: 41786255
Confirm both '76.9' and 76.9 on a decimal(3,1) works as expected.

Where are you seeing 77.0
0
 

Author Comment

by:SiobhanElara
ID: 41786270
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
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 41786291
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
 

Author Comment

by:SiobhanElara
ID: 41786307
That works, as does double-clicking on the cell on phpMYAdmin table view and entering it there.
0
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 41786316
The problem is then in how you are submitting it to the DB - what is running that query?
0
 

Author Comment

by:SiobhanElara
ID: 41786350
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
 
LVL 58

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41786366
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
 

Author Closing Comment

by:SiobhanElara
ID: 41786416
Holy crap, I hang my head in shame. Thank you for pointing out what I should have known all along!
0
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 41786467
You are welcome.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question