SQL raised to the extent of the error "Invalid floating point operation".

Question

I have this code

declare @termtomonths float
set @termtomonths=120/365.00 
set @termtomonths= round(@termtomonths,2,1)*12
select power(1-(1+0.11/12.00), -3.84)

When I run it, it returns an error

    Msg 3623, Level 16, State 1, Line 6
An invalid floating point operation occurred.

Try this formula in MS EXCEL and it works

=1-(1+0.11/12)^-3.84

Expected Result

  0.034432865

How can I convert this excel formula to sql formula?

thank

+4

user1035762 Mar 23 '16 at 6:18

1 answer

Allan S. Hansen · Accepted Answer · 2016-03-23T07:44:24+0000

Your expression is incorrect in SQL. To get the same as in Excel, you have to do this:

SELECT  1 - POWER(( 1 + 0.11 / 12.00 ), -3.84);

This gives you: 0.0344326
Note that you have 1 -the POWER function inside in SQL, but not in Excel.

An error is something that also makes you get a floating point error, because your base cannot be negative.