none
Cast RRS feed

  • Question

  • Getting artmteic error message

    Statement: 

    SELECT ISNULL(CAST(ROUND(((ed.RateOrPct*100)),2)AS DECIMAL(4,2)),0.00), * FROM dedtable

    If the field is 0.40000 it works correctly however if the field is 1.0000 then get error message .  How can I correct to work for both amounts. 

    Wednesday, March 2, 2016 7:57 PM

Answers

  • Hi CIWorker,

    As mentioned above, the code you posted above won't throw such an error, please check if there is any where condition that makes this happen.

    SELECT CAST(12.345 AS varchar(6));
    
    -- Arithmetic overflow error converting numeric to data type varchar.
    SELECT CAST(12.345 AS varchar(5));


    Sam Zha
    TechNet Community Support

    Thursday, March 3, 2016 2:46 AM
    Moderator

All replies

  • Error is due to you cast to DECIMAL(4,2), but your field is 1.0000.

    Just change for DECIMAL(5,2)

    Wednesday, March 2, 2016 8:02 PM
  • The maximum value it can hold is 99.99. You're asking it to hold 100.

    SELECT COALESCE(CAST(ROUND(((1*100)),2)AS DECIMAL(5,2)),0.00)
    SELECT COALESCE(CAST(ROUND(((.4*100)),2)AS DECIMAL(5,2)),0.00)



    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Wednesday, March 2, 2016 8:02 PM
  • That was a sample at .4, I have all %.  Chaging to 5,2 does not work as it continues to error out.
    Wednesday, March 2, 2016 8:15 PM
  • Er.. run the example I posted. DECIMAL(5,2) will hold 100.00. DECIMAL(4,2) will not, and results in an overflow error.

    If you are getting another error please post it.

    Here's a futher example, with every percentant (to 2dp) from 0 to 100:

    DECLARE @numbers TABLE (number DECIMAL(5,4))
    SET NOCOUNT ON
    WHILE (SELECT COALESCE(MAX(number),0) FROM @numbers) < 1
    BEGIN
     INSERT INTO @numbers (number) VALUES
     ((SELECT COALESCE(MAX(number)+.0001,0) FROM @numbers))
    END
    SET NOCOUNT OFF
    
    SELECT COALESCE(CAST(ROUND(((number*100)),2)AS DECIMAL(5,2)),0.00)
      FROM @numbers


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.



    Wednesday, March 2, 2016 8:16 PM
  • Instead of decimal(4,2) use decimal(10,2) - this will be enough.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 2, 2016 8:21 PM
    Moderator
  • I tried that and still get

    Arithmetic overflow error converting numeric to data type varchar.

    Wednesday, March 2, 2016 8:49 PM
  • That sounds like you have non-numeric characters in your string.

    This should find those rows:

    SELECT *
      FROM dedtable
     WHERE ISNUMERIC(rateOrPct) = 0


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, March 2, 2016 8:53 PM
  • Can you please post structure of your dedTable? The error refers to numeric into varchar which is a bit weird. 

    Please also post your complete query and exact error message. If you can post sample of data and desired result, that will be helpful as well.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 2, 2016 9:28 PM
    Moderator
  • Arithmetic overflow error converting numeric to data type varchar.

    As far as I can see that error cannot come from the expresion you posted. There is no conversion to varchar.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, March 2, 2016 10:52 PM
  • Hi CIWorker,

    As mentioned above, the code you posted above won't throw such an error, please check if there is any where condition that makes this happen.

    SELECT CAST(12.345 AS varchar(6));
    
    -- Arithmetic overflow error converting numeric to data type varchar.
    SELECT CAST(12.345 AS varchar(5));


    Sam Zha
    TechNet Community Support

    Thursday, March 3, 2016 2:46 AM
    Moderator