Non numeric data types are implicitly converted to numeric data types if possible. If the conversion is not possible the resulting value is zero.
Once both operands have been converted to a numeric type, the following table is used to determine the final data type of the expression.
|
Operand (A) |
Operand (B) |
Resulting Expression Data Type |
|
bit, tinyint, smallint, or integer |
bit, tinyint, smallint, or integer |
integer |
|
bit, tinyint, smallint, integer, bigint |
bigint |
bigint |
|
bit, tinyint, smallint, integer bigint, decimal, numeric |
decimal, numeric |
numeric |
|
bit, tinyint, smallint, integer, bigint, decimal, numeric, real, float, double |
real, float, double |
double |
|
bit, tinyint, smallint, integer, bigint, decimal, numeric, real, float, double, qfloat |
qfloat |
qfloat |
For integral data type operand combinations, remainders will be lost for multiplication and division operators.
If the resulting data type of the expression is decimal or numeric, the following table is used to determine the precision and scale of the result. P1 & S1 are the precision and scale of the first operand respectively. P2 & S2 are the precision and scale of the second operand respectively. M is a maximum SQL numeric precision of 38.
|
Operator |
Resulting Precision |
Resulting Scale |
|
Addition & Subtraction |
min ( M, max ( P1 - S1, P2 - S2 ) + max ( S1, S2 ) + 1 ) |
max ( S1, S2 ) |
|
Multiplication |
min ( M, P1 + P2 + 1 ) |
min ( M, S1, + S2 ) |
|
Division |
P1 - S1 + S2 + max(6, S1 + P2 + 1) |
max(6, S1 + P2 + 1) |
For division expressions, if the resulting precision exceeds M, then both the resulting precision and resulting scale are reduced equally until the resulting precision equals M. If the resulting scale after reduction is less than 6, it is set to a value of 6.