CODEWITHSUNDEEP
sqlsql-server
Impostor
Impostor

Reputation: 2050

System.Data.Entity.Core.EntityCommandExecutionException An invalid floating point operation occurred

I get the following exception while excecuting this sql command:

SELECT (ACOS(SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)+COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)*COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0))*6371) AS foo

System.Data.Entity.Core.EntityCommandExecutionException An invalid floating point operation occurred”

How do I have to change the query to make it run? My approach was

ROUND( statement, 2)

but it doesn't work.

It's a location search by longitude and latitude

Update: doesn't work with SQL Server 2017 aswell http://sqlfiddle.com/#!18/9eecb/38598

Upvotes: 3

Views: 87

Answers (2)

StepUp
StepUp

Reputation: 38189

The result for this query is 6371:

SELECT (
SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)+COS(PI()
    *51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)*COS(PI()
    *13.7910680057092/180.0-PI()*13.7910680057092/180.0)
)*6371
 AS foo

However, 6371 is not eligible for ACOS. ACOS shoulde be lower or equal to 1.

For example:

SELECT ACOS(1) -- OK
SELECT ACOS(1.1) -- An invalid floating point operation occurred.

UPDATE:

This query could return a value which is greater than 1. So try to use CASE statement:

SELECT 
(
ACOS
(
    CASE WHEN      
        (SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)
        +COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)
        *COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0)) > 1
        THEN 1
    ELSE      
        (SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)
        +COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)
        *COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0)) 
    END 
)
*6371) AS foo

Upvotes: 1

Muhammad Vakili
Muhammad Vakili

Reputation: 708

Round the statement inside ACOS() first

SELECT (ACOS( ROUND(SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)+COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)*COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0), 15))*6371) AS foo

it's because of floating conversation in sql. without rounding, sql server assumes that the input value a little bit bigger than 1, you can round the input, or subtract a little value like 0.00000000000001 before passing it to acos()

Upvotes: 1

Related Questions