Query update Truncated incorrect DOUBLE value

Databases
1

Hi all, hope in your help.

I need update the table tbl_a with this query:

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:

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

I have this error:


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

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

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:

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

Can you help me?
thank you.

2

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.

3

thank you, solved with:

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.

4

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

5

thank you for help.
Solved with:

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 … :wink: