Operations on Decimals

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 or NUMERIC will round the value to a decimal with no fractional part. Example: SELECT CAST('0.5' AS DECIMAL) will return 1 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 and y > 0 then: x - (floor(x / y) * y)
• if x >= 0 and y < 0 then: x % abs(y)
• if x < 0 and y > 0 then: - abs(x) % y
• if x < 0 and y > 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

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)
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.
POWER(base, exponent)	The power function, raising base to the power exponent.
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.
SIGN(value)	The "sign" function: -1, 0, or 1 showing if value is <0, =0, or >0.
SQRT(value)	Result has type DOUBLE. Square root of value. Produces NaN for negative value
TRUNCATE(value)	same as TRUNCATE(value, 0)
TRUNC(value [, digits] )	same as TRUNCATE(value [, digits])
TRUNCATE(value, digits)	where digits is an integer value. Truncates the value to the specified number of decimal digits after the decimal point.