Rounding and Truncation Functions

CEIL( <expr> )

Returns the smallest integer which is greater than or equal to the specified numerical expression.

> select ceil(2.34);
3

FLOOR( <expr> )

Returns the largest integer which is less than or equal to the specified numerical expression.

> select floor(6.34);
6

ROUND( <expr> [, <scale> ] )

Returns the numeric value, rounded off to a given number of decimal places, of the input numeric expression. If the scale factor is not specified, it will round off to the nearest integer.

scale - optional numeric expression, representing the number of decimal places to be rounded off to in a given numeric expression. Default is 0.

> select round(5.678);
6
> select round(5.678, 2);
5.68

ABS( <expr> )

Returns the absolute value of the specified numeric expression.

> select abs(-100);
100

SIGN( <expr> )

The SIGN function returns the sign of its argument:

  • Returns -1 if the argument is negative

  • Returns 1 if the argument is positive

  • Returns 0 if the argument is 0

> select sign(-100);
-1

MOD( <expr> )

Returns the remainder of a number divided by another number

Support datatype: INT/DOUBLE

> select mod(17,3)
2.0

Last updated

#930: Cross account hive GCP

Change request updated