Solved

MySQL decimal field is rounding and shouldn't be

Posted on 2016-09-06
12
51 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 51

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 51

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 56

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 56

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 56

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 56

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 56

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reformat SQL - so SSRS can read the columns 25 31
MS SQL Conditional WHERE clause 3 37
SQL Query help 3 24
Sql case statement to calculate totals 5 34
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 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