ROUND Function

Usage

The ROUND function can accept an optional second argument that specifies the precision of the returned value. The syntax and semantics of the second argument depend on whether the first argument is a number expression, a DATETIME expression, or DATE expression.

Rounding numeric and MONEY values

• When the first argument is a numeric expression, the returned value is a DECIMAL and the second argument can be an integer in the range from -32 to +32 inclusive, specifying the position (relative to the decimal point) of the last significant digit of the returned value. If you omit the factor specification when the first argument is numeric, ROUND returns the integer value of the first argument rounded to a scale of zero, or to the units place.
  Positive-digit values specify rounding to the right of the decimal point; negative-digit values specify rounding to the left of the decimal point, as Examples of negative, zero, and positive rounding factors shows:

  
  
  

  The following example uses the ROUND function with a column expression as its first argument and no second argument, so that the numeric expression is rounded to a scale of zero. This query returns the order number and rounded total price of items whose total price (rounded to the default scale of zero decimal places) is equal to $124.00.

  SELECT order_num , ROUND(total_price) FROM items
     WHERE ROUND(total_price) = 124.00;

  If you use a MONEY data type as the argument for the ROUND function and you round to an explicit or default scale of zero, the returned value is represented with .00 as the fractional part. The SELECT statement in the following example rounds 125.46 and a MONEY column value. The query returns 125 and a rounded price in the form xxx.00 for each row in the items table.

  SELECT ROUND(125.46), ROUND(total_price) FROM items;

Rounding DATE and DATETIME values

• When the first argument to ROUND is a DATETIME expression, the returned value is a DATETIME YEAR TO MINUTE data type and the second argument must be a quoted string that specifies the smallest significant time unit in the returned value. If you omit the second argument, the default format string is 'DD', specifying the nearest day, with the hour and minute rounded to 00:00. The following format strings are valid as the second argument:

  Table 1. Format strings for DATETIME arguments to the ROUND function

  Format String	Effect on Returned DATETIME Value
  'YEAR'	Rounded to the beginning of the nearest year, with dates after June 30 rounded up to the next year. The month, day, hour, and minute values round to -01-01 00:00.
  'MONTH'	Rounded to the beginning of the nearest month. Dates after the 15th are rounded up to the next month. The day, hour, and minute values round to 01 00:00.
  'DD'	Rounded to the beginning (00:00 = midnight) of the nearest day. DATETIME values later than 12:00 noon are rounded up to the next day.
  'DAY'	Rounded to the beginning of the nearest Sunday. Dates that fall on Wednesday, Thursday, Friday, or Saturday are rounded up to the next Sunday.
  'HH'	Rounded to the beginning of the nearest hour. Time of day values with minute:second later than 29:59 are rounded up to the next hour. Minutes round to zero.
  'MI'	Rounded to the beginning of the nearest minute. Time of day values with second later than 30 are rounded up to the next minute.

  If you omit the format string specification after an initial DATETIME expression argument, the returned value is the value of the first argument rounded to the nearest day, as if you had specified 'DD' as the format string.

  Examples that follow use the ROUND function with a column expression that returns a DATETIME YEAR TO FRACTION(5) value in a SELECT statement. In these queries, table mytab has only a single row, and in that row the value of mytab.col_dt is 2012-12-07 14:30:12.12300.

  The following query specifies 'YEAR' as the DATETIME format string:

  SELECT ROUND(col_dt, 'YEAR') FROM mytab;

  The value returned is 2013-01-01 00:00.
  The next query resembles the previous query, but casts the returned value to a DATE data type:

  SELECT ROUND(col_dt, 'YEAR')::DATE FROM mytab;

  The value returned is 01/01/2013.
  This example specifies 'MONTH' as the DATETIME format string:

  SELECT ROUND(col_dt, 'MONTH') FROM mytab;

  The value returned is 2012-12-01 00:00.
  This example rounds the DATETIME expression to YEAR TO HOUR precision:

  SELECT ROUND(col_dt, 'HH') FROM mytab;

  The value returned is 2012-12-07 15:00.
• When the first argument is a DATE expression, the returned value is also a DATE data type if the second argument is a quoted string that specifies the smallest time unit in the returned value. These are the same format strings as for rounding DATETIME values, except that 'HH' and 'MI' are not valid for DATE values. There is no default format string for rounding DATE arguments.

  To return formatted DATE values, you must specify one of the following quoted strings as the second argument to the ROUND function:

  Table 2. Format strings for DATE arguments to the ROUND function

  Format String	Effect on Returned DATE Value
  'YEAR'	Rounded to the beginning of the nearest year. Dates after June 30 are rounded up to the next year. The month and day values each round to 01.
  'MONTH'	Rounded to the beginning of the nearest month. Dates after the 15th are rounded up to the next month. The returned day value is 01.
  'DD'	The DATE value of the first date_expression argument is returned.
  'DAY'	The value is rounded to the nearest Sunday. If the first argument is a Sunday, that date is returned. Dates that fall on Wednesday, Thursday, Friday, or Saturday are rounded up to the next Sunday.

If you specify no format string as the second argument when the first argument is a DATE data type, no format string takes effect as the default. No error is issued, but the first argument is treated as numeric expression that evaluates to an integer, rather than as a DATE value. HCL OneDB™ stores DATE values internally as the integer count of days since 31 December 1899. For dates in the 21st century, integer equivalents to DATE values are 5-digit integers, ranging between approximately 37,000 and 74,000.

For example, the query SELECT ROUND(TODAY) FROM systables provides no format string for a DATE expression, and returns the integer 40999 if the query is issued on 1 April 2012.

SELECT ROUND(TODAY, -2) FROM systables;

On the next day, 2 April 2012, the same query would return the integer value 41000.

SELECT ROUND(TODAY, 'DD') FROM systables WHERE tabid = 1;

sprintf(query1, ", 
   "select round( ?::date, 'DAY') from mytab");
EXEC SQL prepare selectq from :query;
EXEC SQL declare select_cursor cursor for selectq;
EXEC SQL open select_cursor using :hostvar_date_input;
	
EXEC SQL fetch select_cursor into :var_date_output;

For the order of precedence among the HCL OneDB environment variables that can specify the display and data entry formats for the built-in chronological data types, see the topic Precedence of DATE and DATETIME format specifications.