ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Round function in Oracle PL/SQL or SQL Server

Updated on June 29, 2013

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;
Here, ROUND function rounded the date value to the nearest rounding month value
Here, ROUND function rounded the date value to the nearest rounding month value

Sample Exercises

  1. Write down a query which rounds the number ’5555.23456′to 4 places of decimal.
  2. Write down a SQL query which rounds the number ’-5555.23456′to 4 places of decimal.
  3. Write a SQL query which rounds the number 16.123, one digit to the left of the decimal point.
  4. Write down a query which rounds the number ’9.5f′.
  5. Write down a query which rounds the number ’5555.23456′, three digits to the left of the decimal point
  6. Write a PL/SQL query which '1-September-2012′ as the nearest rounding to month.

Exercise answers

Comments

    0 of 8192 characters used
    Post Comment

    • jabelufiroz profile image

      Firoz 3 years ago from India

      Impressive hub. Voted up.

    Click to Rate This Article