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)

• ROUND (number)

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 ])

Parameters

datetime-exp

A datetime expression that identifies a date and time number.

format

A text expression that specifies a format model 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-13 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-13 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

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

01-JAN-93

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

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]

Parameters

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.

Usage 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

SHOW ROUND(2/3, .01)

0.67

SHOW ROUND(2/3, .1)

0.70

SHOW ROUND(2/3, .5)

0.50

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

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

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

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

ROUND(15.193, 1)

15.2

ROUND(15.193, -1)

20