Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B1033902 


View PDF 
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:
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
An expression that identifies a date and time number.
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 211 Format Models for ROUND for Dates and Time
Format Model  Description 

CC
SCC 
One greater than the first two digits of a 4digit 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  4digit 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  Twodigit 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 4digit 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
Example 2110 Rounding to the Nearest Year
When the value of the NLS_DATE_FORMAT option is DDMONYY
, then this statement:
SHOW ROUND ('27OCT92','year')
returns this value:
01JAN93
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) [MULTIPLEDECIMAL]
Arguments
An expression that identifies the number to round.
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.
Specifies that rounding is performed by rounding to the nearest multiple of roundvalue. (Default)
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
Example 2111 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 2112 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 2113 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