ROUND function

lineamovil

Hello!

I have a BO that stores totals (number type) and this field is a calculation of several others.

It works fine, and on screen shows 2 decimals as it is supposed to do. Example: 430.56
Ok thats fine, but I the MySQL Database is stored something like this: 439.5599999999999997

I have been using ROUND function like this:
BO.numberfieldname=ROUND(FIELD,2) and still saves the same in the database.

I dont want to use CEILING or FLOOR, it does not apply for decimal rounding.

Any clue?

Thank you!!!

aware_support

Why are you concerned how this is saved in the database? As long as the user sees the correct values, it should be OK

lineamovil

Thank you SO much for your help.

I need to store a Round number with 2 decimals in the MySQL database because I have 2 third party applications.

1 of them is an Electronic Invoice. (TAX issues)
The other is for the Translate Number to Text. When it converts the total to Text string.

This happens only with some records, not with all of them.

If you have a tip in how to "cut" the rest of the unneeded decimals.

Thank you!!

aware_support

Not sure if anything can be done here other than maybe trying to solve it at the database level - maybe write a database trigger that would convert truncate the number automatically...

lineamovil

Thank you.

I´ll try to find a solution. If I find one, I will post it here. Thanks!

lineamovil

Hey,, I found this and worked for ME!!!!!!

http://www.stemkoski.com/how-to-properly-store-currencymoney-in-mysql/

Just check de image. It worked....

rocketman

I realise this is an old post but the link has died so I just wanted to add my two penneth

I needed to do a comparison on two attributes so created a report that searched for items where the two values were not equal. as in value1<>value2. I got a shed load of entries which appeared to be equal - like, £37.95 and £37.95

When I dug a little deeper it transpires that one was £37.950016756 and the other was £37.950016732, so this line passed the test (not equal) even though the client sees them as equal

So in the Mysql database, I converted the two fields to DECIMAL(12,4) and Voila, the data was truncated in the database (didn't lose anything) and the report came out perfect with no spurious lines.

I did try the various options in AwareIM (Currency, Custom, decimal with 4 places) but they all got created in MySQL as Type DOUBLE

Having read up on stuff relating to the precision required for currency transactions, it does seem to me that AIM could do with having this data type built in as an option

http://www.mysqltutorial.org/mysql-decimal/

BLOMASKY

I have found that any of my numeric fields (that aware creates as FLOATS) that MIGHT have a decimal #, I change them in the database to make them decimal(TotalLength, decimal places) and all is good.

Bruce

rocketman

For completeness I solved the problem from within AIM by modding the search criteria in the query to

.... WHERE ROUND(value1,4)<>ROUND(Value2,4)

But it would be nice to not to have to remember to do that since the functionality is baked into MySQL