- HubPages»
- Technology»
- Computers & Software»
- Computer Software
Round function in Oracle PL/SQL or SQL Server
Round is an Oracle PL/SQL or Microsoft SQL server functions which returns a numeric value and rounded to the specified length or precision or decimal places. ROUND function is one type of Single Row Function and is a build in function.
Usage of ROUND function
- To round any numeric value to some good fashion. Example: 1234
- To round any numeric value up to a certain precision point. Example: from 1234.56789 to 1234.57
- To find out the nearest day, month or year etc of a specified date. For example, the date 01-JAN-14 is the rounded value of 21-DEC-13 and is rounded based on the year value.
ROUND function syntax
ROUND function supports most of the databases like Oracle or Microsoft SQL Wide usage of ROUND function is to create procedure or function into several application's reporting. The syntax of round function is:
ROUND (number, decimal_palaces])
Here,
- number represents the number which we like to round. Number can be numeric expression also.
- decimal_places refers the number of decimal places to round. Decimal_palace value must be integer both positive integer or negative integer. If it is absent, ROUND function rounds the number to 0 decimal palaces.
NOTE: number values are simply rounded up (for positive values). But floating-point numbers are rounded toward the nearest even value.
Syntax to execute
Select ROUND (number, decimal_palaces) From Table_Name
Explanation with examples
Suppose, AccountHistory table contains the account balance information of TD bank's customer with the last date of access. We use ROUND function to get the Round value of the current account balance. Before doing this we simply, will see the details of the AccountHistory table and data. The structure and data are the followings:
Task 1: Find out the account balance of each person where balance is rounded like 1000.
The syntax of ROUND function is ROUND (number, decimal_palaces]). We simply use the round function and it will give the desired output. The syntax will be like the following:
Syntax
select name, account_balance, round(account_balance) from AccountHistory;
ROUND function output
select name, account_balance, round(account_balance,0) from AccountHistory;
If we use the following command, then the output will be the same.
Task 2: Task: Find out the account balance of each person where balance is rounded by 3 decimal values like 1000.001.
The syntax of ROUND function is ROUND (number, decimal_palaces). So to find out the rounded valued of 3 decimal point we have to use 3 for the value of decimal_palaces. And the syntax will be like the following:
Syntax
select name, account_balance, round(account_balance,3) from AccountHistory;
Task 3: Find out the account balance of each person where balance is rounded like 12300.4567 from 12345.67.
The syntax of ROUND function is ROUND (number, decimal_palaces). Look the example carefully. Here, the round operation must round the value from the decimal value. To do this, we have to use negative decimal value. The value is rounded to 2 negative decimal value i.e. -2. The syntax will be the following:
Syntax
select name, account_balance, round(account_balance,-2) from AccountHistory;
Task 4: Find out the last account access date of each person where month is rounded.
We have to use ROUND function and we round that value based on month value. So, the syntax will be like the following:
Syntax
select name, last_account_access_date, ROUND(TO_DATE (last_account_access_date),'MON') from AccountHistory;
Sample Exercises
- Write down a query which rounds the number ’5555.23456′to 4 places of decimal.
- Write down a SQL query which rounds the number ’-5555.23456′to 4 places of decimal.
- Write a SQL query which rounds the number 16.123, one digit to the left of the decimal point.
- Write down a query which rounds the number ’9.5f′.
- Write down a query which rounds the number ’5555.23456′, three digits to the left of the decimal point
- Write a PL/SQL query which '1-September-2012′ as the nearest rounding to month.
