The ROUND function is a built-in function in Oracle’s PL/SQL programming language that is used to round a numeric value to a specified number of decimal places or to the nearest integer.
The PL/SQL ROUND function can be used to round off a date value to a specified format. This function takes two arguments: the first argument is the date value that needs to be rounded, and the second argument is an optional format string that specifies the unit of the date value to round to.
Syntax
The syntax for the ROUND function is as follows:
ROUND (number, [decimal_places]) ROUND( date, [ format ] )
ROUND number
number is the value to be rounded and decimal_places is the number of decimal places to which the number should be rounded. If decimal_places is omitted, the ROUND function rounds the number to the nearest whole number.
ROUND date
The date parameter is a valid date value that needs to be rounded. The format parameter is an optional parameter that specifies the unit of the date value to round to. The format parameter can be one of the following:
‘YYYY’: Round the date to the nearest year.
‘MM’: Round the date to the nearest month.
‘DD’: Round the date to the nearest day.
‘HH24’: Round the date to the nearest hour (24-hour format).
‘MI’: Round the date to the nearest minute.
Example
For example, suppose we have a number 3.14159 that we want to round to two decimal places. We can use the following code:
SELECT ROUND(3.14159, 2) AS result FROM dual;
The result of this query would be 3.14.
In addition to rounding a number to a specified number of decimal places, the ROUND function can also be used to round a number to the nearest integer. To do this, simply omit the decimal_places argument when calling the ROUND function.
For example, suppose we have a number 3.6 that we want to round to the nearest integer. We can use the following code:
SELECT ROUND(3.6) AS result FROM dual;
The result of this query would be 4.
It is worth noting that when rounding a number that is exactly halfway between two possible rounded values, the ROUND function rounds to the nearest even number. This is known as “banker’s rounding” or “round to even”. For example, if we wanted to round the number 2.5 to the nearest integer, the ROUND function would round it to 2, whereas if we wanted to round the number 3.5 to the nearest integer, the ROUND function would round it to 4.
Example of round date
Suppose we have a date value ‘2023-04-09 18:29:59’. If we want to round this date value to the nearest minute, we can use the following code:
SELECT
ROUND(TO_DATE('2023-04-09 18:29:59', 'YYYY-MM-DD HH24:MI:SS'), 'MI')
FROM dual;
This will return the rounded date value as ‘2023-04-09 18:30:00’. Similarly, if we want to round the same date value to the nearest hour, we can use the following code:
SELECT
ROUND(TO_DATE('2023-04-09 18:29:59', 'YYYY-MM-DD HH24:MI:SS'), 'HH24')
FROM dual;
This will return the rounded date value as ‘2023-04-09 18:00:00’.
In conclusion, the ROUND function in Oracle’s PL/SQL programming language is a useful tool for rounding numeric values to a specified number of decimal places or to the nearest integer.
Also, as I wrote at the beginning of the article and showed with examples, the Oracle PL/SQL ROUND function can be used to round off a date value to a specified format. It is a useful function for manipulating date values in a database and can help simplify complex date calculations.