Now if you want to get rid of the ending zero, simply cast it:
select cast((123.456-(123.456%.001)) as decimal (18,2))
Explanation of decimal and numeric (Transact-SQL)
Numeric data types that have fixed precision and scale.
- decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
- Fixed precision and scale numbers. When maximum precision is used, valid values are from – 10^38 +1 through 10^38 – 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
- p (precision)
- The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
- s (scale)
- The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s<= p. Maximum storage sizes vary, based on the precision.
Precision Storage bytes 1 – 9 5 10-19 9 20-28 13 29-38 17Explanation Reff: http://msdn.microsoft.com/en-us/library/ms187746.aspx