EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL round
 

PLSQL round

Updated April 6, 2023

PLSQL round

 

 

Introduction to PL/ SQL round

PL/ SQL round function is used for rounding off a date datatype value or an expression that evaluates to date value to its nearest day, year, century, month, etc format depending on what format is being passed to it as the argument. We can make use of the round function to get the value that is completely rounded as per our requirements and specifications. In this article, we will study how we can make the use of PL SQL round function, study its syntax and the type o format that we can specify for rounding, and will also learn about the implementation of round function along with the help of certain examples. In this topic, we are going to learn about the PLSQL round.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax 

The syntax of the round function in PL/ SQL is as shown below –

ROUND (date expression, format)

The terminologies used in syntax are described below –

Date expression:

This can be any date datatype value or an expression that will evaluate to value which is in date datatype. This is the value that we want to round off to its nearest format as specified by the second parameter. Note that it is necessary and compulsory to specify this argument while using the round function.

Format:

This argument is optional in nature. This is used for specifying the expected format to which the value of date should be rounded. When not specified it rounds the value of the date passed as the first argument to its nearest day value.

Format specification:

Let us now see that how we can specify the format models in which we want the date value to be rounded with the help of the table displayed below showing all the formats and their corresponding descriptions.

Format Details
Q Format the date value to its nearest quarter.
W Format the date value to its nearest day of the week.
CC, SCC Format the date value to its nearest century which can be with or without minus sign in it respectively.
IYYY, IYY, IY, I Format the date value to its nearest ISO year value.
Y, YY, YYY, [S]YYYY, [S]YEAR Specification of year value in various different appearances as per our requirement.
DAY, D, DY Format the date value to its nearest and closest Sunday.
MI Format the date value to minutes.
HH, HH12, HH24 Format the date value to its hours.
WW (ISO), IW Format the date value to its week number.
RM Format the date value to its nearest roman numerals’ month value.
MON Format the date value to its nearest month value in abbreviated name format.
MM Format the date value to its nearest numeric month value.
MONTH Format the date value to its nearest month value displaying its complete name.
DD, J, DDD Format the date value to its nearest day value which is in the day of the month, Julian day, or the day of the year value respectively.
HH24, JJ, HH12 Format the date value to its nearest hours.
W Format the date value to its nearest day of the week.

The output of the round function is the date value which is rounded into the format as specified in the second argument and is in date datatype.

Examples of PLSQL round

Let us now understand how we can make use of the ROUND() function in PL/ SQL along with its implementation with the help of examples.

Example #1

Consider that we have one value in date format as shown below –

'26-Jan-2021 18:20:19'

The above value is to be converted into the date value as it is in character string format and also the format of the converted date value should be the same in which it is provided, we should make the use of the following query statement –

TO_DATE ('26-Jan-2021 18:20:19', 'DD-Mon-YYYY HH24:MI: SS')

The output of the above query statement is as shown below showing the same date value but now in date datatype and not the character string datatype –

PLSQL round output 1

Now, we will consider this value for further examples to show you the behavior of the ROUND () function in PL/ SQL. Suppose, in the beginning, we want to round this date value to its default format where we will not specify any value in the second parameter of the round() function. Our query statement will become as shown below –

SELECT
TO_CHAR(
ROUND( TO_DATE( '26-Jan-2021 18:20:19',  'DD-Mon-YYYY HH24:MI:SS' ) ),
'DD-Mon-YYYY HH24:MI:SS' )  formatted_dateFROM
dual;

The output of the above query statement after execution will be as shown below having the date value rounded to its nearest day.

PLSQL round output 2

Round with integers –

The alternative ROUND function used for integers can also be used for rounding of the decimal value or any number values to its nearest integer value for the value at the right of decimal point with the number of integers to be rounded specified in the second parameter and the value to be rounded as the first parameter. According to the below syntax –

ROUND (n, integer value)

N is the number value to be rounded while integer value helps you to specify the value up to which you want to round the supplied number.

Example:

Let us consider one example where we will try to round the value of decimal number 96.186 to its value in which there is just 1 digit after the decimal point which can be specified in the query statement as shown below –

SELECT ROUND (96.186,1) "Rounded_value" FROM DUAL;

The output of the execution of the above query statement is as shown below –

PLSQL round output 3

We can see that after a decimal point there is just one digit present to which the number is rounded off. If the value after the digit up to which we have to round of is 5 or more than that then the digit value is increased by one or else it is kept as it is.

Conclusion – PLSQL round

The round () function in PL/ SQL can be used with either dates or integers. When used with the date we can also specify the format by using the second argument or it rounds to the nearest day by default. When using integers we can specify the integers or digits up to which we want the date value to round off.

Recommended Articles

We hope that this EDUCBA information on “PLSQL round” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Triggers in PL/SQL
  2. Loops in PL/SQL
  3. PL/SQL Data Types
  4. PLSQL Interview Questions

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW