 How to calculate max value for decimal type • Question

• Hi,

How can I calculate max number that can be stored in SQL decimal type? What's the formula?

For example, what's the max value that can be stored in decimal(18,6) column in SQL server?

Thank you.

Isolda

Friday, May 25, 2012 11:41 PM

• Check out the following logic. You can go up-to (not including ) 10^ (18-6) :

DECLARE @Dec decimal(18,6) = 999999999999 -- 10 ^ (18-6)
SELECT @Dec  -- 999999999999.000000
GO

DECLARE @Dec decimal(18,6) = 9999999999991
SELECT @Dec  -- 999999999999.000000
GO
/*
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

(1 row(s) affected)
*/

DECLARE @Dec decimal(18,6) = 999999999999+.99
SELECT @Dec  --999999999999.990000
GO

Related article: http://www.sqlusa.com/bestpractices2005/moneyformat/

Kalman Toth SQL SERVER & BI TRAINING

Saturday, May 26, 2012 12:00 AM

All replies

• So for a decimal 18,6 the max value would be 999999999999.999999

http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx

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 SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies 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. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies 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.

Chuck Pedretti | Magenic – North Region | magenic.com

Saturday, May 26, 2012 12:00 AM
• Check out the following logic. You can go up-to (not including ) 10^ (18-6) :

DECLARE @Dec decimal(18,6) = 999999999999 -- 10 ^ (18-6)
SELECT @Dec  -- 999999999999.000000
GO

DECLARE @Dec decimal(18,6) = 9999999999991
SELECT @Dec  -- 999999999999.000000
GO
/*
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

(1 row(s) affected)
*/

DECLARE @Dec decimal(18,6) = 999999999999+.99
SELECT @Dec  --999999999999.990000
GO

Related article: http://www.sqlusa.com/bestpractices2005/moneyformat/

Kalman Toth SQL SERVER & BI TRAINING

Saturday, May 26, 2012 12:00 AM
• Thanks, 10 ^ (18-6) is what I was looking for.

I got it

decimal xx22 = Convert.ToDecimal(Math.Pow(10, 18-6)) - Convert.ToDecimal(Math.Pow(10, -6));

Thank u.

Isolda

Saturday, May 26, 2012 12:37 AM
• Max value 12 digits before decimal and 6 digits after the decimal

With Thanks and Regards Sambath Raj.C

• Proposed as answer by Monday, May 28, 2012 10:10 AM
Monday, May 28, 2012 10:10 AM