Decimal Operations

A synonym for the decimal type is numeric.

A decimal number is characterized by two magnitudes: the precision, which is 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 produces a value with no fractional part. Parsing a string as a decimal rounds to the nearest representable value, with ties rounded to even. Example: SELECT CAST('0.5' AS DECIMAL) returns 0, since the scale is 0 and 0.5 is halfway between 0 and 1.

The maximum precision supported is 38 decimal digits. The maximum scale supported is 38 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

Casting a decimal value to another decimal type with a smaller scale truncates the extra fractional digits toward zero. No rounding is performed.

Example while casting from DECIMAL(8, 4) to DECIMAL(6, 2):

Input Value	Output Value
1234.1250	1234.12
-1234.1250	-1234.12
1234.1264	1234.12
1234.1234	1234.12
-1234.1264	-1234.12
-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
BROUND(decimal, digits)	Performs banker's rounding to the specified number of decimal digits after the decimal point. Negative values for "digits" are supported (result is a DECIMAL value).
CEIL(value)	Ceiling function: nearest integer value greater than or equal to argument (result is a DECIMAL value).
DIV_NULL(numerator, denominator)	Like division, but returns NULL when denominator is zero.
FLOOR(value)	Floor function: nearest integer value less than or equal to argument (result is a DECIMAL value).
ROUND(value)	same as ROUND(value, 0).
ROUND(value, digits)	where digits is an integer value, which may be negative. Round the value to the specified number of decimal digits after the decimal point (result is a DECIMAL value).
SIGN(value)	The "sign" function: -1, 0, or 1 showing if value is <0, =0, or >0 (result is a DECIMAL value).
TRUNCATE(value)	same as TRUNCATE(value, 0) (result is a DECIMAL value).
TRUNC(value [, digits] )	same as TRUNCATE(value [, digits]) (result is a DECIMAL value).
TRUNCATE(value, digits)	where digits is an integer value, which may be negative. Truncates the value to the specified number of decimal digits after the decimal point (result is a DECIMAL value).