none
How to calculate max value for decimal type RRS feed

  • 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

Answers

  • 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
    Moderator

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
    Moderator
  • 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 Sambath Raj.C Monday, May 28, 2012 10:10 AM
    Monday, May 28, 2012 10:10 AM