CODEWITHSUNDEEP
sqlsql-serverrounding
djblois
djblois

Reputation: 805

Formula in SQL server is rounding creating wrong result

I have this formula in SQL server:

((FP.Month1/30) * (30 - DAY(GETDATE()))) + FP.month2 + ((FP.month3/30) * (Day(GETDATE())))

I have been testing out results and they are coming out slightly wrong. So I broke it down to the minimal amount, this:

(FP.Month1/30)

and when I run that, it comes out wrong but the reason this comes out wrong is it is ignoring the decimals. Month1 is 140 so if you divide that by 30, it is supposed to come out as 4.666667 but it is coming out as 4 instead. So that will of course result in the total formula resulting in a wrong result. How do I make sure SQL server uses decimals also.

FYI, I don't know if this effects it but the final result needs to be rounded to a whole number.

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

SQL server does integer division. So instead of 30, use 30.0:

(FP.Month1 / 30.0)

As a note, 30.4 is a better estimate of the number of days in a month.

Upvotes: 2

Related Questions