ROUND

Returns the value of expr rounded to scale.

ROUND(NUMBER)

Returns the value of expr rounded to scale.

Syntax

ROUND(expr[, scale])

Arguments

expr: Can be any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.

---

scale (optional): The number of decimal places the output should contain, as an integer.

• If no scale is specified, scale = 0 is assumed, and all decimal places are removed.
• If scale < 0, rounding occurs to the left of the decimal point.

Return Values

The return type depends on the expr type.

If scale is omitted, the return value is ROUND(expr, 0) and is of the same data type as the numeric data type of expr. If scale is specified, a DECIMAL is returned.

Usage Notes

The following rules apply:

• If expr = 0, the ROUND function always returns 0 regardless of scale.
• If expr < 0, the function returns ROUND(expr, scale) - ROUND(-expr, scale).
• If expr > 0, the function returns FLOOR(expr * POWER(10, scale) + 0.5) * POWER(10, -scale).

ROUND(TIMESTAMP)

Returns the value of expr rounded to fmt.

Syntax

ROUND(expr[, fmt])

Arguments

expr: Timestamp or an expression that can be resolved to a TIMESTAMP value.

---

fmt (optional): The return value format:

Format	Rounding or Truncation Rule
CC SCC	One greater than the first two digits of a four-digit year
SYYYY YYYY YEAR SYEAR YYY YY Y	Year (rounded to July 1st)
IYYY IY IY I	Year with the calendar week according to ISO-8601 standard
Q	Quarter (rounded to the sixteenth day of the second month of the quarter)
MONTH MON MM RM	Month (rounded to the sixteenth day)
WW	Same weekday as the first day of the year
IW	Same weekday as the first day of the calendar week according to ISO 8601 standard, i.e., Monday
W	Same weekday as the first day of the month
DDD DD J	Day
DAY DY D	Start day of the week
HH HH12 HH24	Hour
MI	Minute

Return Values

The return value is of data type TIMESTAMP.

Usage Notes

If fmt is omitted, expr is rounded to the nearest day. The date expression must be able to be resolved to a TIMESTAMP value.

Example 1 | ROUND(NUMBER)

Rounding without specifying scale.

SELECT  round(123.45), 
        round(-123.45), 
        round(0.5);

+---------------+----------------+------------+
| ROUND(123.45) | ROUND(-123.45) | ROUND(0.5) |
|---------------+----------------+------------|
|           123 |           -123 |          1 |
+---------------+----------------+------------+

Example 2 | ROUND(NUMBER) scale argument

Rounding with scale specified.

SELECT  round(123.45, 1), 
        round(-123.45, 1), 
        round(-123.45333, 2), 
        round(-123.45633, 2);

+------------------+-------------------+----------------------+----------------------+
| ROUND(123.45, 1) | ROUND(-123.45, 1) | ROUND(-123.45333, 2) | ROUND(-123.45633, 2) |
+------------------+-------------------+----------------------+----------------------+
|            123.5 |            -123.5 |              -123.45 |             ~-123.46 |
+------------------+-------------------+----------------------+----------------------+

Example 3 | ROUND(TIMESTAMP)

select  round(to_date('19-NOV-2023'), 'YEAR') as result1, 
        round(to_date('19-NOV-2023'), 'MONTH') as result2,
        round(to_date('19-NOV-2023'), 'DAY') as result3, 
        round(to_date('19-NOV-2023')) as result4;

+----------------------------+----------------------------+----------------------------+----------------------------+
| RESULT1                    | RESULT2                    | RESULT3                    | RESULT4                    |
+----------------------------+----------------------------+----------------------------+----------------------------+
|  2023-12-31T22:00:00.000Z  |  2023-11-30T22:00:00.000Z  |  2023-11-18T22:00:00.000Z  |  2023-11-18T22:00:00.000Z  |
+----------------------------+----------------------------+----------------------------+----------------------------+