Query update Truncated incorrect DOUBLE value

Hi all, hope in your help.

I need update the table tbl_a with this query:

Code:

---

UPDATE tbl_a a
JOIN tbl_ca ca ON a.XYZ = ca.XYZ
JOIN tbl_cb cb ON a.Amb = cb.Amb
SET a.Amb = ca.Amb,
 a.Dur = cb.Dur,
 a.C_Int = ROUND(((a.A2 * a.Dur) / 60), 2);

---

When adding this line in update query:

Code:

---

a.C_Int = ROUND(((a.A2 * a.Dur) / 60), 2);

---

I have this error:

Code:

---

[Err] 1292 - Truncated incorrect DOUBLE value: '111,00'

---

I tried this query for convert string (varchar) to decimal:

Code:

---

SELECT
        CAST('1.505,00' AS DECIMAL(10, 2)) AS result
FROM
        tbl_a ;

---

But the result is: 1,51 that is different from 1.505,00...

these are the fields of the table but I can not edit directly in the table:

Code:

---

Dur        decimal        10,2
C_Int        varchar, 255
A2        varchar, 255

---

Can you help me?
thank you.

Ah. European numbers. Always fun.

SELECT
CAST('1.505,00' AS DECIMAL(10, 2)) AS result

Should return 1.51; the decimal point for floats is a .

You think you're telling it to cast 1 thousand 505 as a decimal; what you're actually telling it is to cast 1 point 505 as a decimal with two decimal places; so it correctly returns 1.51.

thank you, solved with:

Code:

---

mysql> SELECT
        ID,
        A2,
        ROUND(
                CAST(
                        REPLACE (A2, ".", "") AS DECIMAL (10, 2)
                ),
                2
        ) AS result
FROM
        tbl_m
WHERE
        ID = 833;
+-----+--------+--------+
| ID  | a2    | result |
+-----+--------+--------+
| 833 | 843,33 | 843    |
+-----+--------+--------+
1 row in set

---

Why not see decimal in output `result` ?
Is not sufficient the round syntax?
thank you.

You need to also replace the , with .

You've done this
1.345,66
=>
1345,66

but , is still not recognized as a decimal point.
You need to make it
1.345,66 => 1345.66

thank you for help.
Solved with:

Code:

---

mysql> SELECT
        ID,
        a2,
        ROUND(
                CAST(
                        REPLACE (
                                REPLACE (A2, ".", ""),
                                ",",
                                "."
                        ) AS DECIMAL (10, 2)
                ),
                2
        ) AS result
FROM
        tbl_m
WHERE
        ID = 833;
+-----+--------+--------+
| ID  | a2    | result |
+-----+--------+--------+
| 833 | 843,33 | 843,33 |
+-----+--------+--------+
1 row in set

---

P.S.: I love your city Atlanta and your airport ... ;)