8.4.12 TRUNC

(1) Specification format

scalar-function-TRUNC ::= TRUNC(target-data[,num-digits])
 
  target-data ::= value-expression
  num-digits ::= value-expression

To Page Top

(2) Explanation of specification format

target-data:

Specifies the numeric data to be processed.

The following rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify numeric data for the target data. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.

• You cannot specify a dynamic parameter by itself for the target data.

num-digits:

Specifies the number of digits.

The following rules apply:

• Specify num-digits in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify INTEGER type or SMALLINT type data for num-digits.

If the value specified for num-digits is positive (n), it leaves n decimal places of the target data and truncates the decimal places at position n + 1 and beyond. If the value specified for num-digits is negative (-n), it truncates n digits from the integer portion of the target data.

If num-digits is omitted, the target data is truncated to 0 decimal places.

The following example illustrates the result of executing the scalar function TRUNC.

Example

For numeric data 123.456, truncate everything past the second decimal place.

TRUNC(123.456,2) → 123.450

To Page Top

(3) Rules

1. If you specify a dynamic parameter for num-digits, the assumed data type of the dynamic parameter is INTEGER.

2. The length of the data type of the execution result will be the length of the data type of the argument numeric-data.

3. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

4. If target-data or num-digits has a null value, the execution result will be a null value.

5. Because data of the DOUBLE PRECISION type includes an error, you must be careful when using the data for the scalar function TRUNC. For example, you will not obtain expected calculation results in the following case:

   TRUNC(2.172157E4,2) -> 2.1721560000000001E4

   This is due to the fact that, because 0.01 does not have a finite binary representation, 0.01 and 1.0E-02 are not exactly equal. If you want an exact value for the execution result of truncation, use DECIMAL-type data as the target data.

6. The following table shows the data types that can be specified for the argument and the corresponding valid ranges for num-digits.

Table 8‒8: Data types that can be specified for the argument and valid ranges for num-digits

No.	Data type specified for the argument (numeric data)	Valid range of num-digits
1	INTEGER	-18 to 0
2	SMALLINT	-9 to 0
3	DECIMAL(m,n)	-(m - n - 1) to n
4	DOUBLE PRECISION	-308 to 323

Legend: m, n: Positive integers

Notes:

If the value specified for num-digits falls outside the valid range, it does not result in an error. For a positive value outside the valid range, truncation does not occur. For a negative value outside the valid range, the result will be 0.

The following examples illustrate the results for different values of num-digits.

Example 1:

The following SQL statement is executed on table T1, assuming the value of column C1 is 123456789, and its type is INTEGER:

SELECT TRUNC("C1",x) FROM "T1"

The results of executing TRUNC("C1",x) for different values of x are shown in the following table.

Table 8‒9: Execution results for the SQL statement for different values of x

Value of x	Result of TRUNC(C1,x)
1 or greater	123456789
0	123456789
-1	123456780
-8	100000000
-9 or less	0

Example 2:

The following SQL statement is executed on table T1, assuming the value of column C2 is 123.45 and its type is DECIMAL(5,2):

SELECT TRUNC("C2",y) FROM "T1"

The results of executing TRUNC("C2",y) for different values of y are shown in the following table.

Table 8‒10: Execution results for the SQL statement for different values of y

Value of y	Result of TRUNC(C2,y)
3 or greater	123.45
2	123.45
1	123.40
0	123.00
-1	120.00
-2	100.00
-3 or less	0.00

To Page Top

(4) Examples

Example 1:

Retrieve that data in columns C2 and C3 from table T1 and truncate the decimal portion starting at position 3, leaving 2 decimal places.

SELECT TRUNC("C2",2),TRUNC("C3",2) FROM "T1"

Example 2:

Truncate 2 digits from the integer portion of the data in columns C1 to C3 from table T1.

SELECT TRUNC("C1",-2),TRUNC("C2",-2),TRUNC("C3",-2) FROM "T1"

To Page Top