ceiling function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

Returns the smallest number not smaller than expr rounded up to targetScale digits relative to the decimal point. This function is a synonym of ceil function.

Syntax

ceiling(expr [, targetScale])

Arguments

  • expr: An expression that evaluates to a numeric.
  • targetScale: An optional INTEGER literal greater than -38 specifying to how many digits after the decimal points to round up.

Returns

If no targetScale is given:

  • If expr is DECIMAL(p, s), returns DECIMAL(p - s + 1, 0).
  • For all other cases, returns a BIGINT.

If targetScale is specified and expr is a:

  • TINYINT

    Returns a DECIMAL(p, 0) with p = max(3, -targetScale + 1).

  • SMALLINT

    Returns a DECIMAL(p, 0) with p = max(5, -targetScale + 1).

  • INTEGER

    Returns a DECIMAL(p, 0) with p = max(10, -targetScale + 1)).

  • BIGINT

    Returns a DECIMAL(p, 0) with p = max(20, -targetScale + 1)).

  • FLOAT

    Returns a DECIMAL(p, s) with p = max(14, -targetScale + 1)) and s = min(7, max(0, targetScale))

  • DOUBLE

    Returns a DECIMAL(p, s) with p = max(30, -targetScale + 1)) and s = min(15, max(0, targetScale))

  • DECIMAL(p_in, s_in)

    Returns a DECIMAL(p, s) with p = max(p_in - s_in + 1, -targetScale + 1)) and s = min(s_in, max(0, targetScale))

If targetScale is negative the rounding occurs to -targetScale digits to the left of the decimal point.

The default targetScale is 0, which rounds up to the next bigger integral number.

Examples

> SELECT ceiling(-0.1);
 0

> SELECT ceiling(5);
 5

> SELECT ceiling(5.4);
 6

> SELECT ceiling(3345.1, -2);
 3400

> SELECT ceiling(-12.345, 1);
 -12.3