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.

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 –

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.

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 –

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

This is a guide to PLSQL round. Here we discuss the implementation of the round function along with the help of certain examples. You may also have a look at the following articles to learn more –

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