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:
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..
DATETIME
ROUND(datetime_exp, [format ])
A datetime 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.
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
.
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'
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.
DECIMAL (when the round type is MULTIPLE)
NUMBER (when the round type is DECIMAL)
ROUND(number_exp roundvalue) [MULTIPLE|DECIMAL]
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
.
(Default) Specifies that rounding is performed by rounding to the nearest multiple of roundvalue.
Specifies that rounding is performed by rounding to the number of decimal places indicated by roundvalue.
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.
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