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

Question

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

Answer 1

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()