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)
Thanks for the article, good one
thanks for your tips..
It save my time..
Regards,
Infoyen
Hi pinal sir,
Thanks for this article.
Regard$
Chirag Satasiya
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
SQRT of a negative number will result a *complex* number.
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.
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.
Thank you!!
And just FYI, your blog has helped me SO VERY MUCH in my job as an analyst. Thank you thank you thank you!