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
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
Comments
Post a Comment