T-SQL Error Msg 3623 - Invalid Floating Point

Transact-SQL
1

Hello,

I am trying to calculate bearing using the following:

Bearing = ATN2(SIN(DestinationLongitude-OriginLongitude) * COS(DestinationLatitude),(COS(OriginLatitude) * SIN(DestinationLatitude)) - (SIN(OriginLatitude) * COS(DestinationLatitude) * COS(DestinationLongitude-OriginLongitude)))

I keep getting Msg 3623 An invalid floating point operation occurred. Any idea how to fix this?

2

likely some of your sub-expressions are getting too large or too small. Try taking it apart to see what the intermediate values are and what exactly triggers the error

3

Updated code to:
Bearing = round(ATN2( COS( RADIANS(OriginLatitude))*SIN( RADIANS(DestinationLatitude) ) - SIN( RADIANS(OriginLatitude) )*COS( RADIANS(DestinationLatitude) )*COS( RADIANS(DestinationLongitude-OriginLongitude) ),COS( RADIANS(DestinationLatitude) )*SIN( RADIANS(DestinationLongitude-OriginLongitude) ) )*180/PI() % 360, 2)

Now getting data types float and int are incompatible in the modulo operator. Any suggestions on a fix for this? My goal is to calculate bearing with this formula. Works perfectly in Excel but not so much in SSMS. Maybe there is another way to accomplish this?

Thanks for the help!

4

The modulo operator (%) can be used only on numeric data types (i.e., INT and DECIMAL). For example this should work:

DECLARE @x FLOAT = 775.7;
SELECT CAST(@x AS DECIMAL(19,5))%360

So cast the expression to the left of the modulo operator to decimal with sufficient precision and scale.

5

Thank you James!