CODEWITHSUNDEEP
sqlsql-serversql-server-2008rounding
mschapiro
mschapiro

Reputation: 25

Error in sql with rounding

I have a very weird problem with SQL. I have a cell with a number 22, when I want to round it, the result is incorrect.

If I try with the number instead of the cell, the code works well.

select Mora2, round(Mora2/30,0)*30, mora2+1, 
round((mora2+1)/30,0)*30, round(convert(float,mora2)/30,0)*30
from Trimestre

The result of that is 

Mora2   (No column name)    (No column name)    (No column name)    (No column name)

 22         30                  23                  30                  30

If i try with the number, the code works well.

 select round(22/30,0)*30 as Mora2

 Mora2
 0

Why can be possible that?

Thanks!

Upvotes: 0

Views: 45

Answers (1)

Hart CO
Hart CO

Reputation: 34784

You can CAST(Mora2 AS INT) whenever you're using it to strip off the decimal portion and ensure integer division.

When either the numerator or denominator is not an integer, the result of the division will have decimal places. So 22.0/30 = 0.733333 while 22/30 = 0

Upvotes: 1

Related Questions