DECIMAL type

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Represents numbers with a specified maximum precision and fixed scale.

Syntax 

{ DECIMAL | DEC | NUMERIC } [ (  p [ , s ] ) ]

p: Optional maximum precision (total number of digits) of the number between 1 and 38. The default is 10. s: Optional scale of the number between 0 and p. The number of digits to the right of the decimal point. The default is 0.

Limits

The range of numbers:

  • -1Ep + 1 to -1E-s

  • 0

  • +1E-s to +1Ep - 1

For example a DECIMAL(5, 2) has a range of: -999.99 to 999.99.

Literals 

decimal_digits { [ BD ] | [ exponent BD ] }
| digit [ ... ] [ exponent ] BD

decimal_digits:
[ + | - ] { digit [ ... ] . [ digit [ ... ] ]
            | . digit [ ... ] }

exponent:
E [ + | - ] digit [ ... ]

digit: Any numeral from 0 to 9.

The BD postfix and exponent E are case insensitive.

Examples 

> SELECT +1BD;
  1

> SELECT 5E3BD;
  5000

> SELECT 5.321E2BD;
  532.1

> SELECT -6.45
  -6.45

> SELECT typeof(6.45);
  DECIMAL(3,2)

> SELECT CAST(5.345 AS DECIMAL(3, 2));
  5.35

> SELECT typeof(CAST(5.345 AS DECIMAL));
  DECIMAL(10, 0)

> SELECT typeof(CAST(5.345 AS DECIMAL(2)));
  DECIMAL(2, 0)