ROUND numeric:
.-,--0-------------------. >>-ROUND--(--numeric-expression1--+------------------------+--)->< '-,--numeric-expression2-'
ROUND datetime:
.-,--'DD'---------------------------. >>-ROUND--(--datetime-expression--+-----------------------------------+--)->< '-,--format-string--+-------------+-' '-locale-name-'
The schema is SYSIBM. The SYSFUN version of the ROUND numeric function continues to be available.
If numeric-expression1 is positive, a digit value of 5 or greater is an indication to round to the next higher positive number. For example, ROUND(3.5,0) = 4. If numeric-expression1 is negative, a digit value of 5 or greater is an indication to round to the next lower negative number. For example, ROUND(-3.5,0) = -4.
If the expression is a decimal floating-point data type, the DECFLOAT rounding mode will not be used. The rounding behavior of ROUND corresponds to a value of ROUND_HALF_UP. If a different rounding behavior is wanted, use the QUANTIZE function.
If numeric-expression2 is not negative, numeric-expression1 is rounded to the absolute value of numeric-expression2 number of places to the right of the decimal point.
If numeric-expression2 is negative, numeric-expression1 is rounded to the absolute value of numeric-expression2+1 number of places to the left of the decimal point.
If the absolute value of a negative numeric-expression2 is larger than the number of digits to the left of the decimal point, the result is 0. For example, ROUND(748.58,-4) = 0. If numeric-expression1 is positive, a digit value of 5 is rounded to the next higher positive number. If numeric-expression1 is negative, a digit value of 5 is rounded to the next lower negative number.
The data type and length attribute of the result are the same as the data type and length attribute of the first argument, except that the precision is increased by one if the numeric-expression1 is DECIMAL and the precision is less than 31. For example, an argument with a data type of DECIMAL(5,2) results in DECIMAL(6,2). An argument with a data type of DECIMAL(31,2) results in DECIMAL(31,2). The scale is the same as the scale of the first argument.
If either argument can be null or if the argument is not a decimal floating-point number and the database is configured with dft_sqlmathwarn set to YES, the result can be null. If either argument is null, the result is the null value.
This function is not affected by the setting of the CURRENT DECFLOAT ROUNDING MODE special register, even for decimal floating-point arguments. The rounding behavior of ROUND corresponds to a value of ROUND_HALF_UP. If you want behavior for a decimal floating-point value that conforms to the rounding mode specified by the CURRENT DECFLOAT ROUNDING MODE special register, use the QUANTIZE function instead.
Allowable values for format-string are listed in the table of format elements listed below.
The following format elements are used to identify the rounding or truncation unit of the datetime value in the ROUND, ROUND_TIMESTAMP, TRUNCATE and TRUNC_TIMESTAMP functions.
Format element | Rounding or truncating unit | ROUND example | TRUNCATE example |
---|---|---|---|
CC SCC |
Century Rounds up to the start of the next century after the 50th year of the century (for example on 1951-01-01-00.00.00). Not valid for TIME argument. |
Input Value: 1897-12-04-12.22.22.000000 Result: |
Input Value: 1897-12-04-12.22.22.000000 Result: |
SYYYY YYYY YEAR SYEAR YYY YY Y |
Year Rounds up on July 1st to January 1st of the next year. Not valid for TIME argument. |
Input Value: 1897-12-04-12.22.22.000000 Result: |
Input Value: 1897-12-04-12.22.22.000000 Result: |
IYYY IYY IY I |
ISO Year Rounds up on July 1st to the first day of the next ISO year. The first day of the ISO year is defined as the Monday of the first ISO week. Not valid for TIME argument. |
Input Value: 1897-12-04-12.22.22.000000 Result: |
Input Value: 1897-12-04-12.22.22.000000 Result: |
Q | Quarter Rounds up on the 16th day of the second month of the quarter. Not valid for TIME argument. |
Input Value: 1999-06-04-12.12.30.000000 Result: |
Input Value: 1999-06-04-12.12.30.000000 Result: |
MONTH MON MM RM |
Month Rounds up on the 16th day of the month. Not valid for TIME argument. |
Input Value: 1999-06-18-12.12.30.000000 Result: |
Input Value: 1999-06-18-12.12.30.000000 Result: |
WW | Same day of the week as the first day of the
year. Rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the year. Not valid for TIME argument. |
Input Value: 2000-05-05-12.12.30.000000 Result: |
Input Value: 2000-05-05-12.12.30.000000 Result: |
IW | Same day of the week as the first day of the
ISO year. See "WEEK_ISO scalar function" for details. Rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the ISO year. Not valid for TIME argument. |
Input Value: 2000-05-05-12.12.30.000000 Result: |
Input Value: 2000-05-05-12.12.30.000000 Result: |
W | Same day of the week as the first day of the
month. Rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the month. Not valid for TIME argument. |
Input
Value: 2000-06-21-12.12.30.000000 Result: |
Input Value: 2000-06-21-12.12.30.000000 Result: |
DDD DD J |
Day Rounds up on the 12th hour of the day. Not valid for TIME argument. |
Input Value: 2000-05-17-12.59.59.000000 Result: |
Input Value: 2000-05-17-12.59.59.000000 Result: |
DAY DY D |
Starting day of the week. Rounds up with respect to the 12th hour of the 4th day of the week. The first day of the week is based on the locale (see locale-name). Not valid for TIME argument. |
Input Value: 2000-05-17-12.59.59.000000 Result: |
Input Value: 2000-05-17-12.59.59.000000 Result: |
HH HH12 HH24 |
Hour Rounds up at 30 minutes. |
Input Value: 2000-05-17-23.59.59.000000 Result: |
Input Value: 2000-05-17-23.59.59.000000 Result: |
MI | Minute Rounds up at 30 seconds. |
Input Value: 2000-05-17-23.58.45.000000 Result: |
Input Value: 2000-05-17-23.58.45.000000 Result: |
SS | Second Rounds up at half a second. |
Input Value: 2000-05-17-23.58.45.500000 Result: |
Input Value: 2000-05-17-23.58.45.500000 Result: |
If a format element that applies to a time part of a value is specified for a date argument, the date argument is returned unchanged. If a format element that is not valid for a time argument is specified for a time argument, an error is returned (SQLSTATE 22007).
VALUES (
ROUND(873.726, 2),
ROUND(873.726, 1),
ROUND(873.726, 0),
ROUND(873.726,-1),
ROUND(873.726,-2),
ROUND(873.726,-3),
ROUND(873.726,-4) )
This example returns:
1 2 3 4 5 6 7
--------- --------- --------- --------- --------- --------- ---------
873.730 873.700 874.000 870.000 900.000 1000.000 0.000
VALUES (
ROUND(3.5, 0),
ROUND(3.1, 0),
ROUND(-3.1, 0),
ROUND(-3.5,0) )
This example returns: 1 2 3 4
---- ---- ---- ----
4.0 3.0 -3.0 -4.0
VALUES (
ROUND(DECFLOAT('3.12350'), 3))
This example
returns: 1
-----
3.12400
SET :RND_DATE = ROUND(DATE('2000-08-16'), 'MONTH');
The
value set is 2000-09-01. SET :RND_TMSTMP = ROUND(TIMESTAMP('2000-08-14-17.30.00'),
'YEAR');
The value set is 2001-01-01-00.00.00.000000.