4

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?

5

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;
  • 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
  • Early mySql was more than a bit lax with type checking, Mr B's test shows it may still be in some places. It often used to take what you declare is as guidance of what you wish to store, taking DECIMAL(3,3) as you want to be able to store at least everything that encompasses, but it is free to use a wider type and not enforce the smaller, where other engines are more strict and take the same as meaning you want to store everything that encompasses and absolutely nothing more. @jcalfee314: Perhaps Firebird does similar? Maybe add it to your tags to attract a Firebird expert's attention? – David Spillett Oct 29 '18 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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