SQL SERVER – Fix : Error 3623 – An invalid floating point operation occurred

Going back in time, I always had a problem with mathematics. It was a great subject and I loved it a lot but I only mastered it after practices a lot. I learned that mathematics problems should be addressed systematically and being verbose is not a trick, I learned to solve any problem. Recently one of reader sent me an email with the title “Mathematics problem – please help!” and I was a bit scared. I was good at mathematics but not the best. When I opened the email I was relieved as it was Mathematics problem with SQL Server. My friend received following error while working with SQL Server.

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

The reasons for the error is simply that invalid usage of the mathematical function is attempted. Let me give you a few examples of the same.

SELECT SQRT(-5);
SELECT ACOS(-3);
SELECT LOG(-9);

If you run any of the above functions they will give you an error related to invalid floating point. Honestly there is no workaround except passing the function appropriate values. SQRT of a negative number will give you result in real numbers which is not supported at this point of time as well LOG of a negative number is not possible (because logarithm is the inverse function of an exponential function and the exponential function is NEVER negative).

When I send above reply to my friend he did understand that he was passing incorrect value to the function. As mentioned earlier the only way to fix this issue is finding incorrect value and avoid passing it to the function. Every mathematics function is different and there is not a single solution to identify erroneous value passed. If you are facing this error and not able to figure out the solution. Post a comment and I will do my best to figure out the solution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

13 thoughts on “SQL SERVER – Fix : Error 3623 – An invalid floating point operation occurred

  1. Just in addition, you will have no problem with +ve values on those functions, like

    1. SELECT SQRT(5);
    2. SELECT ACOS(3)
    3. SELECT LOG(9);

    Only number 2 [ACOS(3)] throws error, because we need to pass floating expression, alas passing the floating expression also throws error, please execute this one

    SELECT ACOS(2.555665555)

    But if execute this one…
    SELECT ACOS(0.55555), the generated result is found, I meant the error is not thrown. So in case of ACOS fundtion we have to be cautious to choose the value.

    Thanks
    ~~sp_who

    Like

  2. Pls i need help, after installing my reason 5 it will open at that instant, but later when i try to open, it will show me ‘floating point R6002 not supproted. I dont understand the floating point. Pls help me i love reason 5 more than any other software. Even more than fl studio 10. My email.

    Like

  3. Let suppose you have to calculate 5√(-15.0) (fifth root of -15), which is the same as POWER(-15.0, 0.2). The correct result of this expression is -1.730080613. But if you try to run

    SELECT POWER (-15.0, 0.2)

    you will get “An invalid floating point operation occurred.” error.

    You’ll get the same error in all cases where the base is a negative number and exponent is not an integer.

    Like

  4. succeeds:
    select Cos((90 – 38.588032) / (180 / PI())) * Cos((90 – 38.588032) / (180 / PI())) + Sin((90 – 38.588032) / (180 / PI())) * Sin((90 – 38.588032) / (180 / PI())) * Cos((-90.585533 – -90.585533) / (180 / PI()))

    fails:
    select Acos(Cos((90 – 38.588032) / (180 / PI())) * Cos((90 – 38.588032) / (180 / PI())) + Sin((90 – 38.588032) / (180 / PI())) * Sin((90 – 38.588032) / (180 / PI())) * Cos((-90.585533 – -90.585533) / (180 / PI())))

    Like

    • I think the floating point expression being passed to ACOS() is “floating” to the wrong side of 1, resulting in an invalid argument being passed. Casting the cosine expression to a fixed-precision type like DECIMAL works, as long as the decimal precision is less than float effect…

      Like

  5. Hi Mr Pinal
    I have some floating field problem
    i have 2 field , field A as float, field B as float
    in a record field A has 0.15 value and field B has 0.15 value also (Using Select statement)
    but when i use “Select fieldA – fieldB From tableName Where KeyField = Key” the result is 2.77555756156289E-17

    i encountered the problem when i have unexpected result from my stored procedure that determine deviation of the two fields. i still dunno why this happening (i’m using sql server 2008)

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s