Post reply

Arithmetic overflow error converting varchar to data type numeric.

  • October 30, 2014 at 1:50 pm

    #300699

    OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting

    (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX. Any advice would be great pulling my hair out, I know it should be fairly simple, I am overlooking something. Tyhank You in advance.

    Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7))

    round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1

    SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))

  • October 30, 2014 at 2:38 pm

    #1755921

    From the examples that you posted, I could only test the last one and the problem is with your precision which comes short. Changing it to 11 makes it work. Be aware that float is an approximate data type and will return unexpected data.

    SELECT CAST('735509.00053' AS DECIMAL(11,5))

    - CAST('735509.00047' AS DECIMAL(11,5)),

    convert(FLOAT,CAST('735509.00053' AS DECIMAL(11,5)))

    - convert(FLOAT,CAST('735509.00047' AS DECIMAL(11,5)))

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply