MySQL Anomaly with DECIMAL fields

To keep this short and simple, please look at the following two lines to reproduce the problem. I need a solution to store this information NOT floating calculations that trail off into random forever-ness (see my other questions which is why I am using DECIMAL(10,2) in the first place).

Problem is simple. There is an amount being queried from one of my tables for 2479 that requires some calculation, and then insert into another table. Both tables have the field as DECIMAL(10,2) to fix the FLOATing point issues as outlined in my previous post.

The following line of code will reproduce the issue without need for any tables
SELECT CAST(FORMAT(CEIL((2791/1.13) * 100)/100,2) AS DECIMAL(10,2));

Open in new window


Why does it not return this amount that the following line returns ??
SELECT FORMAT(CEIL((2791/1.13) * 100)/100,2);

Open in new window


Thanks in advance.
LVL 3
microvbAsked:
Who is Participating?
 
microvbAuthor Commented:
Interesting,

It works when I do this >>

SELECT CAST((CEIL((2791/1.13) * 100)/100) AS DECIMAL(10,2));

Open in new window

0
 
microvbAuthor Commented:
Problem resolved. Dunno why, but it works --- so..... meh!
0
 
Dave BaldwinFixer of ProblemsCommented:
The comma ',' generated by the FORMAT statement is causing an error in the DECIMAL statement.  If you use
SELECT CAST((CEIL((2791/1.13)*100)/100) AS DECIMAL(10,2));

Open in new window


it will work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.