Please help with Floating Point problem... An invalid floating point operation occurred.

_{Wednesday, January 29, 2020 9:37 PM}

Hello and thank you for your help... since yesterday I have a problem that is making my head turn! I have a small formula that calculates the distance of zipcodes in a radius, like this:

SELECT Z.Zipcode, Z.[Latitude], Z.[Longitude],  ( 
    3959 * acos( 
        cos( radians(32.73) ) * cos( radians( Z.[Latitude]) ) * cos( radians( Z.[Longitude] ) - radians(-97.32) ) + sin( radians(32.73) ) * sin( radians( Z.[Latitude] ) ) 
    ) 
) AS [Distance] FROM [Zipcode Geo Location] as Z ORDER BY z.Zipcode

Everything works properly until we get to the 76166 zipcode, which has Lat 32.73 and Lon -97.32, I have simplified and replaced the values on the code this way:

SELECT '76166' AS myzip,  ( 
        3959 * acos( --> this line causes problem...         
                (
                    (
                    cos( radians(32.73) ) * cos( radians( 32.73) ) * cos( radians( -97.32 ) - radians(-97.32) ) 
                    )
                    + 
                    (
                    sin( radians(32.73) ) * sin( radians( 32.73 ) )
                    )
                )           
        ) --> closing block...
    ) AS [Distance]
    , (
        3959 * acos(1)
    ) as test

Now, I do some calculations inside the problematic block, and if you can test the inside block, it returns 1 as value... now the problematic line in other words should run like this: "

3959 * acos(1) --> as 1 is the result of all the calculations inside the parentesis... 

if I run the whole code I get this: "An invalid floating point operation occurred.", now I did a test below, manually typing 1, and it runs without any problem resulting in 0... (which is the distance of the zipcode)... my question is... why I get an error if the value is taken from calculation inside that returns 1? 

I tried to convert and/or cast the number resulting from the parenthesis block inside, no success... PLEASE HELP!!

_{Wednesday, January 29, 2020 9:52 PM}

 SELECT '76166' AS myzip,  ( 
        3959 * acos( --> this line causes problem...         
                Cast((
                    (
                    cos( radians(32.73) ) * cos( radians( 32.73) ) * cos( radians( -97.32 ) - radians(-97.32) ) 
                    )
                    + 
                    (
                    sin( radians(32.73) ) * sin( radians( 32.73 ) )
                    )
                )   as int)     
        ) --> closing block...
    ) AS [Distance]
    , (
        3959 * acos(1)
    ) as test           
 

_{Wednesday, January 29, 2020 10:59 PM}

Now, I do some calculations inside the problematic block, and if you can test the inside block, it returns 1 as value... now the problematic line in other words should run like this: "

No it returns a value > 1, as you can tell from the error message. Keep in mind that just because you see a 1, it is not necessarily exctly 1. It is an approximation in base 10 of a floating-point number that is exact in base 2.

Run this to see what the actual number is:

SELECT '76166' AS myzip,  ( --      3959 * acos( --> this line causes problem...            1-         (
               (
               cos( radians(32.73) ) * cos( radians( 32.73) ) * cos( radians( -97.32 ) - radians(-97.32) )                )
               +                (
               sin( radians(32.73) ) * sin( radians( 32.73 ) )
               )
            )         --      ) --> closing block...
   ) AS [Distance]
   , (
      3959 * acos(1)
   ) as test

The correcet action is to have something like:

WITH CTE AS (
   SELECT cos +... AS value
)
SELECT acos (CASE WHEN value > 1 THEN 1 WHEN value < -1 THEN -1 ELSE value END)

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

_{Thursday, January 30, 2020 7:55 AM}

Hi PerezGuillermo8,

The error indicates that you were passing the function inappropriate values, the value may >1 or <-1. In your situation, although the calculation result is 1, there might be something unbecoming in the floating data type.

Like Li's posting, please convert the data type to int or decimal(16,15).

Best Regards,

Lily

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Thursday, January 30, 2020 2:24 PM}

Tried this also, and it did not work, as I did post in my first message, I tried to cast or convert the value but the problem persist.  It can cast the value, but the ACOS function return an error.

_{Friday, January 31, 2020 8:05 AM}

Tried this also, and it did not work, as I did post in my first message, I tried to cast or convert the value but the problem persist.  It can cast the value, but the ACOS function return an error.

So did you look at my post? There you have the correct solution. You need to cap the values at 1 and -1 to handles the imperfections with float values.

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