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 (https://blog.sqlauthority.com)
19 Comments. Leave new
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!
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())))
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…
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)
thanks for the tip…i forgot about the negatives causing an issue :)
ACOS value, should not be 1
–Not working—-
select 69.002 * ( 57.2958 *
(ACOS(
COS( 0.0174533 * 38.27 )*
COS( 0.0174533 * (3827/100.0 ))*
COS(0.0174533 * ((-104.63) – (-10463/100.0))) +
SIN(0.0174533 * 38.27 ) * SIN(0.0174533 * (3827/100.0)))
)
) OrgDistance
–Working—-
select 69.002 * ( 57.2958 *
(ACOS(
convert(decimal(18,4),
COS( 0.0174533 * 38.27 )*
COS( 0.0174533 * (3827/100.0 ))*
COS(0.0174533 * ((-104.63) – (-10463/100.0))) +
SIN(0.0174533 * 38.27 ) * SIN(0.0174533 * (3827/100.0)))
)
)
) OrgDistance
Regards,
Subir
I have no idea what you wanted to tell.
thank you so much, sqrt was killing me as i didnt realise it couldnt take neg values!!
Hi Pinal,
I have a sql user function that is failing with “invalid floating point operation occurred”.
I don´t want to fix the error, I only want to manege it : if the error occurs, then return 9, that means “Error” for me, but the function shoudn’t fail.
Because is a sql user function, I can’t use try catch.
How can I manage this error?
Thank you in advance.
BEGIN TRY
SELECT SQRT(-5);
END TRY
BEGIN CATCH
PRINT ‘Error Occurred’
END CATCH
Thanks for deleting my previous comment:( I did figure out the problem which was that CASTING a varchar to numeric(15,5) was not creating a valid number in certain circumstances, throwing the floating point error. To more accurately convert the varchar to a number I used the following…. CAST(LEFT(latitude, (CHARINDEX(‘.’, latitude, 1) + 5)) AS numeric(15,5))
It would have been nice to have left my issue so perhaps others could find the solution easily…
LOL. just change the data type to Numeric to get around this issue.