Decimal data type
A synonym for the decimal
type is numeric
.
A decimal number is characterized by two magnitudes: the precision, which his the total number of decimal digits represented, and the scale, which is the count of digits in the fractional part, to the right of the decimal point. For example, the number 3.1415 has a precision of 5 and a scale of 4.
The type NUMERIC(precision, scale)
specifies both precision and
scale, both of which must be constants.
The type NUMERIC(precision)
is the same as NUMERIC(precision, 0)
.
The type NUMERIC
is the same as NUMERIC(MAX_PRECISION, 0)
.
[!WARNING] This means that casting to
DECIMAL
orNUMERIC
will round the value to a decimal with no fractional part. Example:SELECT CAST('0.5' AS DECIMAL)
will return1
as the scale is 0.
The maximum precision supported is 28 decimal digits. The maximum scale supported is 10 decimal digits.
Operations available for the decimal
type
The legal operations are +
(plus, unary and binary), -
(minus,
unary and binary), *
(multiplication), /
(division), %
(modulus).
Modulus happens as follows:
For: mod = x % y
- if
x >= 0
andy > 0
then:x - (floor(x / y) * y)
- if
x >= 0
andy < 0
then:x % abs(y)
- if
x < 0
andy > 0
then:- abs(x) % y
- if
x < 0
andy > 0
then:- abs(x) % abs(y)
Division or modulus by zero cause a runtime error.
Casting a string to a decimal value will produce a run time error if parsing fails.
Rounding while casting between Decimal types
Rounding is performed using to nearest, ties away from zero strategy.
Example while casting from DECIMAL(8, 4)
to DECIMAL(6, 2)
:
Input Value | Output Value |
---|---|
1234.1250 | 1234.13 |
-1234.1250 | -1234.13 |
1234.1264 | 1234.13 |
1234.1234 | 1234.12 |
-1234.1264 | -1234.13 |
-1234.1234 | -1234.12 |
Invalid casts between Decimal types
While casting to decimal types, if the current decimal number cannot be represented with the specified precision and scale, a run time error is thrown.
Example:
Valid casts such as: CAST('1234.1234' AS DECIMAL(6, 2))
will return 1234.12
.
But invalid casts such as: CAST('1234.1234' AS DECIMAL(6, 3))
will throw a run time error.
Predefined functions on Decimal Values
ROUND(value) | same as ROUND(value, 0) |
ROUND(value, digits) | where digits is an integer value. Round the value to the specified number of decimal digits after the decimal point. |
TRUNC(value [, digits] ) | same as TRUNCATE(value [, digits]) |
TRUNCATE(value) | same as TRUNCATE(value, 0) |
TRUNCATE(value, digits) | where digits is an integer value. Truncates the value to the specified number of decimal digits after the decimal point. |
ABS(value) | absolute value |
CEIL(value) | Ceiling function: nearest integer value greater than or equal to argument (result is a decimal value) |
FLOOR(value) | Floor function: nearest integer value less than or equal to argument (result is a decimal value) |
SIGN(value) | The "sign" function: -1, 0, or 1 showing if value is <0, =0, or >0. |
POWER(base, exponent) | The power function, raising base to the power exponent. |
SQRT(value) | Result has type DOUBLE. Square root of value. Produces NaN for negative value |
LN(value) | The natural logarithm of value. Produces a runtime error for values less than or equal to zero. |
LOG10(value) | The logarithm base 10 of value. Produces a runtime error for values less than or equal to zero. |