DB2 Version 9.7 for Linux, UNIX, and Windows

ROUND scalar function

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.

The ROUND function returns a rounded value of:

A number, rounded to the specified number of places to the right or left of the decimal point, if the result of the first argument is a numeric value
A datetime value, rounded to the unit specified by format-string, if the first argument is a DATE, TIME, or TIMESTAMP

ROUND numeric

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.

numeric-expression1

An expression that must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, or numeric data type. If the value is not a numeric data type, it is implicitly cast to DECFLOAT(34) before evaluating the function.

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.

numeric-expression2

An expression that returns a value that is a built-in numeric data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the 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.

ROUND datetime

If datetime-expression has a datetime data type, the ROUND function returns datetime-expression rounded to the unit specified by the format-string. If format-string is not specified, datetime-expression is rounded to the nearest day, as if 'DD' is specified for format-string.

datetime-expression: An expression that must return a value that is a date, a time, or a timestamp. String representations of these data types are not supported and must be explicitly cast to a DATE, TIME, or TIMESTAMP for use with this function; alternatively, you can use the ROUND_TIMESTAMP function for a string representation of a date or timestamp.
format-string: An expression that returns a built-in character string data type with an actual length that is not greater than 254 bytes. The format element in format-string specifies how datetime-expression should be rounded. For example, if format-string is 'DD', a timestamp that is represented by datetime-expression is rounded to the nearest day. Leading and trailing blanks are removed from the string, and the resulting substring must be a valid format element for the type of datetime-expression (SQLSTATE 22007). The default is 'DD', which cannot be used if the data type of datetime-expression is TIME.
Allowable values for format-string are listed in the table of format elements listed below.
locale-name: A character constant that specifies the locale used to determine the first day of the week when using format element values DAY, DY, or D. The value of locale-name is not case sensitive and must be a valid locale (SQLSTATE 42815). For information on valid locales and their naming, see "Locale names for SQL and XQuery". If locale-name is not specified, the value of the special register CURRENT LOCALE LC_TIME is used.

The result of the function has the same DATE type as datetime-expression. The result can be null; if any argument is null, the result is the null value.

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.

Table 1. Format elements for ROUND, ROUND_TIMESTAMP, TRUNCATE, and TRUNC_TIMESTAMP
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: 1901-01-01-00.00.00.000000	Input Value: 1897-12-04-12.22.22.000000 Result: 1801-01-01-00.00.00.000000
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: 1898-01-01-00.00.00.000000	Input Value: 1897-12-04-12.22.22.000000 Result: 1897-01-01-00.00.00.000000
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: 1898-01-03-00.00.00.000000	Input Value: 1897-12-04-12.22.22.000000 Result: 1897-01-04-00.00.00.000000
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: 1999-07-01-00.00.00.000000	Input Value: 1999-06-04-12.12.30.000000 Result: 1999-04-01-00.00.00.000000
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: 1999-07-01-00.00.00.000000	Input Value: 1999-06-18-12.12.30.000000 Result: 1999-06-01-00.00.00.000000
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: 2000-05-06-00.00.00.000000	Input Value: 2000-05-05-12.12.30.000000 Result: 2000-04-29-00.00.00.000000
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: 2000-05-08-00.00.00.000000	Input Value: 2000-05-05-12.12.30.000000 Result: 2000-05-01-00.00.00.000000
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: 2000-06-22-00.00.00.000000	Input Value: 2000-06-21-12.12.30.000000 Result: 2000-06-15-00.00.00.000000
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: 2000-05-18-00.00.00.000000	Input Value: 2000-05-17-12.59.59.000000 Result: 2000-05-17-00.00.00.000000
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: 2000-05-21-00.00.00.000000	Input Value: 2000-05-17-12.59.59.000000 Result: 2000-05-14-00.00.00.000000
HH HH12 HH24	Hour Rounds up at 30 minutes.	Input Value: 2000-05-17-23.59.59.000000 Result: 2000-05-18-00.00.00.000000	Input Value: 2000-05-17-23.59.59.000000 Result: 2000-05-17-23.00.00.000000
MI	Minute Rounds up at 30 seconds.	Input Value: 2000-05-17-23.58.45.000000 Result: 2000-05-17-23.59.00.000000	Input Value: 2000-05-17-23.58.45.000000 Result: 2000-05-17-23.58.00.000000
SS	Second Rounds up at half a second.	Input Value: 2000-05-17-23.58.45.500000 Result: 2000-05-17-23.58.46.000000	Input Value: 2000-05-17-23.58.45.500000 Result: 2000-05-17-23.58.45.000000

Note: The format elements in Table 1 must be specified in uppercase.

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

Notes

Determinism: ROUND is a deterministic function. However, the following invocations of the function depend on the value of the special register CURRENT LOCALE LC_TIME.
- Round of a datetime value when locale-name is not explicitly specified and one of the following is true:
  - format-string is not a constant
  - format-string is a constant and includes format elements that are locale sensitive
  Invocations of the function that depend on the value of a special register cannot be used wherever special registers cannot be used.

Examples

Calculate the value of 873.726, rounded to 2, 1, 0, -1, -2, -3, and -4 decimal places, respectively.

   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

Calculate using both positive and negative numbers.

   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

Calculate the decimal floating-point number 3.12350 rounded to three decimal places.
```
   VALUES (
     ROUND(DECFLOAT('3.12350'), 3))
```
This example returns:
```
1
-----
3.12400
```
Set the host variable RND_DT with the input date rounded to the nearest month value.
```
   SET :RND_DATE = ROUND(DATE('2000-08-16'), 'MONTH');    
```
The value set is 2000-09-01.
Set the host variable RND_TMSTMP with the input timestamp rounded to the nearest year value.
```
   SET :RND_TMSTMP = ROUND(TIMESTAMP('2000-08-14-17.30.00'),
                           'YEAR');
```
The value set is 2001-01-01-00.00.00.000000.