ceil function (Databricks SQL)

Returns the smallest number not smaller than expr rounded up to targetScale digits relative to the decimal point.

Syntax

ceil(expr [, targetScale])

Arguments

  • expr: An expression that evaluates to a numeric.

  • targetScale: An optional INTEGER literal greater than -38 specifying by 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.

This function is a synonym of ceiling function (Databricks SQL).

Examples

> SELECT ceil(-0.1);
 0

> SELECT ceil(5);
 5

> SELECT ceil(5.4);
 6

> SELECT ceil(3345.1, -2);
 3400

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