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 (for dates and time)

• ROUND (for numbers)

ROUND (for dates and time)

When a DATETIME expression is specified as an argument, the ROUND function returns a date and time value rounded to a specified date format. When you do not specify a format, the date and time value is rounded to the nearest day.

Return Value

DATETIME

Syntax

ROUND(datetime_exp, format)

Arguments

datetime-exp

An 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.

Table 21-1 Format Models for ROUND for Dates and Time

Format Model	Description
CC SCC	One greater than the first two digits of a 4-digit year to indicate the next century. For example, 1900 becomes 2000. S indicates that BC dates are marked with a negative (-) prefix.
D DAY DY	Starting day of the week (1 to 7). The day of the week that is number 1 is controlled by NLS_TERRITORY.
DD	Day of month (1 to 31).
DDD	Day of year (1 to 366).
HH HH12	Hour of day (1 to 12).
HH24	Hour of day (0 to 23).
IW	Same day of the week as the first day of the ISO year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard.
J	Julian day; that is, the number of days since January 1, 4712 BC.
MI	Minute (0 to 59).
MM	Two-digit numeric abbreviation of month (01 to 12, where January is 01); month rounds up on the sixteenth day.
MON	Abbreviated name of the month; month rounds up on the sixteenth day.
MONTH	Name of the month padded with blanks to 9 characters; month rounds up on the sixteenth day.
Q	Quarter of year (1, 2, 3, 4; JAN to MAR is Q1); quarter rounds up on the sixteenth day of the second month of the quarter.
RM	Roman numeral month (I to XII, where January is I); month rounds up on the sixteenth day.
WW	Same day of the week as the first day of the year.
W	Same day of the week as the first day of the month.
YEAR SYEAR	Nearest year, spelled out (rounds up on July 1). S indicates that BC dates are marked with a negative (-) prefix.
YYYY SYYYY	Nearest 4-digit year (rounds up on July 1). S indicates that BC dates are marked with a negative (-) prefix.
YYY YY Y	Last 3, 2, or 1 digit(s) of nearest year (rounds up on July 1).

Examples

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

01-JAN-93

ROUND (for numbers)

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

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

DECIMAL

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

Notes

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

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. You can use LT and test whether the absolute difference between the two numbers is less than what you regard as significant. For example, you can subtract the two numbers, use the absolute value function, and then compare the result to .01.

The statement

SHOW ABS(expense - 100.00) LT .01

produces the following result.

YES

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