SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 ...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •