Tuesday, July 27, 2010

SQL Server and decimal rounding

As part of some analysis I was doing, I needed to take exactly 2 decimal places of a number. For some reason my query was not outputting data I had expected. I pinpointed this down to rounding when converting to a decimal of only 2 decimal places.

For Example:
select cast(10.554 as decimal(5,2)) --returns 10.55
select cast(10.555 as decimal(5,2)) --returns 10.56

To fix this issue all I needed to do was shift the decimal place to the right a couple digits, take the integer portion, and then shift the decimal place to the left a couple digits.

select cast(cast(10.555  * 100 as int) / 100.0 as decimal(5,2)) --returns 10.55

As an alternative approach, we can use the modulus to extract the value after the decimal place we no longer need.

select (10.555%.01) --returns .005

Then subtract the result from the original number:

select cast(10.555 - (10.555%.01) as decimal(5,2)) --returns 10.55