TRUNC Function

The TRUNC function resembles the ROUND function, but truncates (rather than rounds to the nearest whole number) any portion of its first argument that is smaller than the least significant digit or time unit within the precision that its second argument specifies.

• For numeric expressions, TRUNC replaces with zero any digits less than the specified precision.
• For DATE or DATETIME expressions, TRUNC replaces any time units smaller than the format specification with 1 for month or day time units, or with 0 for time units smaller than day.

The TRUNC function can accept an optional second argument that specifies the precision of the returned value.

• When the first argument is a numeric expression, the second argument must 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, TRUNC returns the value of the first argument truncated to a scale of zero, or to the units place.
  Positive digit values specify truncation to the right of the decimal point; negative digit values specify truncation to the left, as Examples of negative, zero, and positive truncation factors shows.

  
  
  

  The following example calls the TRUNC function with a column expression that returns a numeric value in a SELECT statement. This statement displays the order number and truncated total price of items whose total price (truncated to the default scale of zero decimal places) is equal to $124.00.

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

  If a MONEY data type is the argument in a call to the TRUNC function that specifies a scale of zero, the fractional part becomes .00 in the returned value. For example, the following SELECT statement truncates 125.46 and a MONEY column value. It returns 125 and a truncated price in the form xxx.00 for each row in the items table.

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

• When the first argument to TRUNC is a DATETIME expression, the second argument must be a quoted string that specifies the smallest significant time unit in the returned value. Only the following format strings are valid as the second argument:

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

  Format String	Effect on Returned Value
  'YEAR'	Truncated to the beginning of the year. The month, day, hour, and minute values truncate to 01-01 00:00.
  'MONTH'	Truncated to the beginning of the first day of the month. The hour and minute values round to 00:00.
  'DD'	Truncated to the beginning (00:00 = midnight) of the same day.
  'DAY'	If the first argument is a Sunday, midnight (00:00) on that date is returned. For any other day of the week, midnight on the previous Sunday is returned.
  'HH'	Truncated to the beginning of the hour. The minute value truncates to zero.
  'MI'	Truncated to the beginning of the nearest minute. As for all of these format strings, time units smaller than minute are discarded.

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

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

  This query specifies 'YEAR' as the DATETIME format string:

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

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

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

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

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

  The value returned is 2006-12-01 00:00.
  The following example truncates the DATETIME expression to YEAR TO HOUR precision:

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

  The value returned is 2006-12-07 14:00.
• When the first argument is a DATE expression, the second argument should generally be a quoted string that specifies the smallest time unit in the returned value. These are the same format strings as for truncating DATETIME values, except that 'HH' and 'MI' are not valid for dates, and there is no default format string for truncating DATE expression arguments.

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

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

  Format String	Effect on Returned Value
  'YEAR'	Truncated to the beginning of the year. The month and day values are each 01.
  'MONTH'	Truncated to the beginning of the month. The day value is 01.
  'DD'	The DATE value of the first date_expression argument is returned.
  'DAY'	If the first argument is a Sunday, that date is returned. For any other day of the week, the date of the previous Sunday is returned.

If you apply a numeric format specification as the second argument, nonnegative numbers have no effect on DATE values, but the following example rounds the last two digits of the returned value to zero:

SELECT TRUNC(TODAY, -2) FROM systables;

For applications where integer dates like 39500 are unhelpful, use the 'YEAR', 'MONTH', 'DAY', or 'DD' format strings as the second argument to the TRUNC function, to prevent the DATE expression from being processed as if it were a number expression. On 1 April 2008, the following query returns the DATE value 04/01/2008 if MDY4/ is the setting of the DBDATE environment variable:

SELECT TRUNC(TODAY, 'DD') FROM systables;

If you are using a host variable to store a truncated point-in-time value in dynamic SQL, and the data type of the first argument is not known at prepare time, HCL OneDB assumes that a DATETIME data type is the first argument to the TRUNC function and returns a DATETIME YEAR TO MINUTE truncated value. At execution time, after the statement is prepared, error -9750 is issued if a DATE value is supplied for the host variable. To prevent this error, you can specify the data type for the host variable by using a cast, as in this program fragment.

sprintf(query2, "%s", 
   "select trunc( ?::date, 'DAY') from mytab");
EXEC SQL prepare selectq from :query2;
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;