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