Working with decimal and numeric

select 123.456-(123.456%.001)

returns 123.450

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))

returns 123.45

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 17
Advertisements
By simplemsexchange Posted in SQL 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s