ROUND

Depending on the syntax you specify, the ROUND function performs a numeric operation or a date and time operation. Because the syntax for the ROUND function differs for each type of operation, there are two topics for the ROUND function:


ROUND (datetime)

The ROUND (datetime) function returns date rounded to the unit specified by the format model fmt. The value returned is always of data type DATETIME, even if you specify a different datetime data type for date..

Return Value

DATETIME

Syntax

ROUND(datetime_exp, [format ])

Arguments

datetime-exp

A datetime expression that identifies a date and time number.

format

A text expression that specifies one of the format models shown in the following table. A format model indicates how the date and time number should be rounded.

If you omit this argument, then datetime-exp is rounded to the nearest day.

Table 8-6 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 8-6 Datetime Format Templates for the ROUND and TRUNC Date Functions

Format Model Rounding or Truncating Unit
CC
SCC

One greater than the first two digits of a four-digit year

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y

Year (rounds up on July 1)

IYYY
IY
IY
I

ISO Year

Q

Quarter (rounds up on the sixteenth day of the second month of the quarter)

MONTH
MON
MM
RM

Month (rounds up on the sixteenth day)

WW

Same day of the week as the first day of the year

IW

Same day of the week as the first day of the ISO year

W

Same day of the week as the first day of the month

DDD
DD
J

Day

DAY
DY
D

Starting day of the week

HH
HH12
HH24

Hour

MI

Minute


The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.

Examples

Example 8-81 Rounding to the Nearest Year

When the value of the NLS_DATE_FORMAT option is DD-MON-YY, then this statement:

SHOW ROUND (TO_DATE('27-OCT-92'),'year')

returns this value:

01-JAN-93

Example 8-82 Rounding to Different Formats

Assume that you have a variable named mydatetime with the following definition and value defined in your analytic workspace.

DEFINE MYDATETIME VARIABLE DATETIME
DATE_FORMAT = 'MON-RRRR-DD-HH24'
mydatetime = CURRENT_TIMESTAMP
SHOW mydatetime
= 'AUG-2006-07-13'

As the following SHOW statements illustrate, depending on what date format value you specify, the ROUND function returns different values for the mydatetime variable.

SHOW ROUND(mydatetime, 'RRRR')
01-JAN-07
 
SHOW ROUND(mydatetime, 'MON')
01-AUG-06
 
SHOW ROUND(mydatetime, 'DD')
08-AUG-06
 
SHOW ROUND(mydatetime)
= 'AUG-2006-08-00'

ROUND (number)

When a number is specified as an argument, the ROUND function returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number.

Return Value

DECIMAL (when the round type is MULTIPLE)

NUMBER (when the round type is DECIMAL)

Syntax

ROUND(number_exp roundvalue) [MULTIPLE|DECIMAL]

Arguments

number_exp

An expression that identifies the number to round.

roundvalue

A value that specifies the basis for rounding.

When the round type is MULTIPLE:

  • number_exp is rounded to the nearest multiple of roundvalue.

  • roundvalue can be an INTEGER or decimal number.

 

When the round type is DECIMAL:

  • roundvalue specifies the number of places to the right or left of the decimal point to which number_exp should be rounded. When roundvalue is positive, digits to the right of the decimal point are rounded. When it is negative, digits to the left of the decimal point are rounded.

  • When roundvalue is omitted, number_exp is rounded to 0 decimal places.

  • roundvalue must be an INTEGER.

MULTIPLE

(Default) Specifies that rounding is performed by rounding to the nearest multiple of roundvalue.

DECIMAL

Specifies that rounding is performed by rounding to the number of decimal places indicated by roundvalue.

Notes

Using ROUND to Compare Expressions

A DECIMAL value might be stored in a slightly different form than shows up at the level of significant digits you are using. This small difference can cause unexpected results when you are comparing two expressions. The problem can occur even when you are comparing INTEGER expressions that involve calculations because many calculations are done only after converting INTEGER values to DECIMAL values. You do not generally see the difference in reports because reports usually show only two or three decimal places.

For example, when you compare two numbers with the EQ or NE operators, you probably want to ignore any difference caused by the least significant digits. When expense was stored as 100.00000001, the least significant digit would not be ignored by the simple form of the comparison.

The statement

SHOW expense EQ 100.00

produces the following result.

NO

However, you can use ROUND to force EQ or NE to ignore the least significant digits.

SHOW ROUND(expense, .01) EQ 100.00

This statement produces the following result.

YES

Using ABS to Compare Expressions

When speed of calculation is important in your application, you may want to use the ABS function with LT to compare numbers, instead of using ROUND with EQ or NE.

Examples

Example 8-83 Rounding to Different Multiples

The following statements show the results of rounding the expression 2/3 to different multiples. The value of the DECIMALS setting is 2.

The statement

SHOW ROUND(2/3, .01)

produces the following result.

0.67

The statement

SHOW ROUND(2/3, .1)

produces the following result.

0.70

The statement

SHOW ROUND(2/3, .5)

produces the following result.

0.50

Example 8-84 Rounding to the Nearest Thousand

The following example shows sales rounded to the nearest thousand.

LIMIT month TO FIRST 4
LIMIT district TO FIRST 1
REPORT ROUND(sales 1000)

These statements produce the following output.

DISTRICT: BOSTON
               -------------ROUND(SALES 1000)-------------
               -------------------MONTH-------------------
PRODUCT          Jan95      Feb95      Mar95      Apr95
-------------- ---------- ---------- ---------- ----------
Tents           32,000.00  33,000.00  43,000.00  58,000.00
Canoes          66,000.00  76,000.00  92,000.00 126,000.00
Racquets        52,000.00  57,000.00  59,000.00  69,000.00
Sportswear      53,000.00  59,000.00  63,000.00  68,000.00
Footwear        91,000.00  87,000.00 100,000.00 108,000.00

Example 8-85 Rounding to the Nearest Multiple of 12

To show units rounded to the nearest multiple of 12, use the following statements.

LIMIT month TO FIRST 4
LIMIT district TO FIRST 1
REPORT DECIMAL 0 ROUND(units 12)

These statements produce the following output.

DISTRICT: BOSTON
               --------------ROUND(UNITS 12)--------------
               -------------------MONTH-------------------
PRODUCT          Jan95      Feb95      Mar95      Apr95
-------------- ---------- ---------- ---------- ----------
Tents                 204        204        264        360
Canoes                348        396        480        660
Racquets              996      1,080      1,116      1,308
Sportswear          1,092      1,212      1,296      1,404
Footwear            2,532      2,400      2,772      2,976

Example 8-86 Rounding to Decimal Places

The following statements show the results of rounding 15.193 to various decimal places.

The statement

ROUND(15.193, 1)

produces the following result

15.2

The statement

ROUND(15.193, -1)

produces the following result

20