The `ROUND` function in Oracle is used to round a number or a date to a specified precision. The precision can be in terms of decimal places for numbers or to the nearest specified date part for dates.
# Syntax for Numbers
ROUND(number, decimal_places)
- `number`: The numeric value to be rounded.
- `decimal_places`: Optional. The number of decimal places to round to. If omitted, the default is 0 (round to the nearest integer).
# Examples for Numbers
# 1. **Rounding to the Nearest Integer**
Round the number 123.456 to the nearest integer.
SELECT ROUND(123.456) AS rounded_value FROM DUAL;
-- Returns 123
# 2. **Rounding to a Specified Number of Decimal Places**
Round the number 123.456 to 2 decimal places.
SELECT ROUND(123.456, 2) AS rounded_value FROM DUAL;
-- Returns 123.46
# 3. **Rounding to Zero Decimal Places**
Round the number 123.456 to zero decimal places (default behavior).
SELECT ROUND(123.456, 0) AS rounded_value FROM DUAL;
-- Returns 123
# 4. **Rounding to a Negative Number of Decimal Places**
Round the number 12345.678 to the nearest thousand.
SELECT ROUND(12345.678, -3) AS rounded_value FROM DUAL;
-- Returns 12000
# 5. **Rounding Negative Numbers**
Round the number -123.456 to the nearest integer.
SELECT ROUND(-123.456) AS rounded_value FROM DUAL;
-- Returns -123
# Syntax for Dates
ROUND(date, format)
- `date`: The date value to be rounded.
- `format`: Optional. The format to round the date to. Common formats include 'YEAR', 'MONTH', 'DAY', etc.
# Examples for Dates
# 1. **Rounding to the Nearest Day**
Round the current date to the nearest day.
SELECT ROUND(SYSDATE, 'DD') AS rounded_date FROM DUAL;
-- Rounds the time component to 00:00:00
# 2. **Rounding to the Nearest Month**
Round the current date to the nearest month.
SELECT ROUND(SYSDATE, 'MONTH') AS rounded_date FROM DUAL;
-- Returns the first day of the next month if the current date is on or after the 16th, otherwise the first day of the current month
# 3. **Rounding to the Nearest Year**
Round the current date to the nearest year.
SELECT ROUND(SYSDATE, 'YEAR') AS rounded_date FROM DUAL;
-- Returns the first day of the next year if the current date is on or after July 1st, otherwise the first day of the current year
# 4. **Rounding to the Nearest Quarter**
Round the current date to the nearest quarter.
SELECT ROUND(SYSDATE, 'Q') AS rounded_date FROM DUAL;
-- Returns the first day of the next quarter if the current date is in the second half of the quarter, otherwise the first day of the current quarter
# Practical Use Cases
# 1. **Rounding Financial Data**
Round a salary to two decimal places.
SELECT ROUND(salary, 2) AS rounded_salary FROM employees;
# 2. **Rounding to Nearest Year for Birthdates**
Round an employee's birthdate to the nearest year.
SELECT ROUND(birthdate, 'YEAR') AS rounded_birthdate FROM employees;
# 3. **Rounding Sales Data to Nearest Thousand**
Round the sales amount to the nearest thousand for better readability.
SELECT ROUND(sales_amount, -3) AS rounded_sales FROM sales;
# Additional Examples
# 1. **Rounding to Specific Date Formats**
Round the current date to the nearest month and display it in a specific format.
SELECT TO_CHAR(ROUND(SYSDATE, 'MONTH'), 'YYYY-MM-DD') AS rounded_date FROM DUAL;
# 2. **Combining ROUND with Other Functions**
Combine `ROUND` with other mathematical functions.
SELECT ROUND(AVG(salary), 2) AS avg_rounded_salary FROM employees;
These examples demonstrate the versatility of the `ROUND` function in Oracle for both numerical and date data, helping to format and round values for various practical purposes.