none
Why an SQL Server db field [DataType is:Decimal(5,4)] cannot accept number like (12.333)

    Question

  • Hello Msdn,

            I have a databases field say 'a' Decimal(5,4). It only accepting number with digit to the right . But according to Microsoft documentations 1 2 here it defines in a confused way. Could anyone give me a brief idea of what they mean by precision and scale in decimal type declaration????????

    When I tried to enter 12.333 [precision 5, scale 4], it shown exception, Why it is happening so?


    salampv.07


    • Edited by salam.p.v Thursday, December 20, 2012 7:03 AM
    Thursday, December 20, 2012 7:00 AM

Answers

  • The precision refers to maximum number of decimal digits that can be sotred (including the left and right of the decimal point). The scale referes to the maximum number of digits that can be stored right of decimal point:

    So DECIMAL (2, 1) means you can have a two digit number and one digit at the right of decimal point, I.e; 1.2, 3.5, 2.1, 3.0 are all valid and you can store upto 9.9, DECIMAL (3, 1)  represents three digit in total and one decimal to the right of decimal point, i.e. 20.2, 30.5, 10.0 are all valid up to 99.9.

    In other words:

    The maximum value a DECIMAL (5, 1) can hold is : 9999.9

    The maximum value a DECIMAL (5, 2) can hold is : 999.99

    The maximum value a DECIMAL (5, 3) can hold is : 99.999

    The maximum value a DECIMAL (5, 4) can hold is : 9.9999. So in this case adding 12.333 to DECIMAL (5, 4) cause an overflow error.


    Krishnakumar S

    • Marked as answer by salam.p.v Thursday, December 20, 2012 7:49 AM
    Thursday, December 20, 2012 7:25 AM

All replies

  • The precision refers to maximum number of decimal digits that can be sotred (including the left and right of the decimal point). The scale referes to the maximum number of digits that can be stored right of decimal point:

    So DECIMAL (2, 1) means you can have a two digit number and one digit at the right of decimal point, I.e; 1.2, 3.5, 2.1, 3.0 are all valid and you can store upto 9.9, DECIMAL (3, 1)  represents three digit in total and one decimal to the right of decimal point, i.e. 20.2, 30.5, 10.0 are all valid up to 99.9.

    In other words:

    The maximum value a DECIMAL (5, 1) can hold is : 9999.9

    The maximum value a DECIMAL (5, 2) can hold is : 999.99

    The maximum value a DECIMAL (5, 3) can hold is : 99.999

    The maximum value a DECIMAL (5, 4) can hold is : 9.9999. So in this case adding 12.333 to DECIMAL (5, 4) cause an overflow error.


    Krishnakumar S

    • Marked as answer by salam.p.v Thursday, December 20, 2012 7:49 AM
    Thursday, December 20, 2012 7:25 AM
  •           Thank you Krishnakumar. for the reply, did you see any Microsoft official explanation for this?

    Regards

    salam


    salampv.07

    Thursday, December 20, 2012 7:50 AM