Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3?

What about DECIMAL 3,3?

Does this mean that the precision is a floating decimal place or static that must have 3 decimal places even if they are all zero?

share|improve this question
    
Which DBMS are you talking about? PostgreSQL? SQL Server? DB2? –  a_horse_with_no_name Aug 5 '12 at 15:23

1 Answer 1

up vote 4 down vote accepted

The precision is the total number of decimal digits that can be stored on both sides of the decimal place. The scale is the total number of decimal digits to the right of the decimal point which can be less than or equal to the precision. Therefore the maximum value that can be stored in a DECIMAL(3,3) or a NUMERIC(3,3) is 0.999. The easiest way to find this out is to actually try it out.

Using mysql 5.5.20 the below returns 0.999:

SET @val = (SELECT CAST(1 AS DECIMAL(3,3)));
SELECT @val; 

Whereas the following returns 1:

SET @val = (SELECT CAST(1 AS DECIMAL(3,0)));
SELECT @val;

I hope this helps.

EDIT: I forgot to mention that on most platforms attempting to perform the above will result in some type of Arithmetic Overflow Exception. For instance the below statement running on Microsoft SQL Server 2008 will throw an error:

DECLARE @val DECIMAL(3,3);
SET @val = 1;
SELECT @val;
share|improve this answer
    
That is what we were thinking. We were confused when we were able to store the value 1 in a column like this in Firebird version 1.2 and version 2.5. This SQL returns 1 => SELECT CAST(1 AS DECIMAL(3,3)) FROM RDB$DATABASE –  jcalfee314 Sep 6 '12 at 18:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.