The INTEGER function returns a large integer (a binary integer with a precision of 31 bits) representation of a value of a different data type.

The schema is SYSIBM.

Numeric to INTEGER

numeric-expression

An expression that returns a value of any built-in numeric data type.

The result is the same number that would occur if the argument were assigned to a large integer column or variable.
The fractional part of the argument is truncated.
If the whole part of the argument is not within the range of integers, an error is returned (SQLSTATE 22003).

String to INTEGER

string-expression

An expression that returns a value that is a character-string or Unicode graphic-string representation of a number with a of length not greater than the maximum length of a character constant.

The result is the same number that would result from CAST(string-expresssion AS INTEGER).
Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming an integer, decimal, floating-point, or decimal floating-point constant (SQLSTATE 22018).
If the whole part of the argument is not within the range of integers, an error is returned (SQLSTATE 22003).
The data type of string-expression must not be CLOB or DBCLOB (SQLSTATE 42884).

Date to INTEGER

date-expression

An expression that returns a value of the DATE data type.
The result is an INTEGER value representing the date as yyyymmdd.

Time to INTEGER

time-expression

An expression that returns a value of the TIME data type.
The result is an INTEGER value representing the time as hhmmss.

Boolean to INTEGER

boolean-expression

An expression that returns a Boolean value (TRUE or FALSE).
The result is either 1 (for TRUE) or 0 (for FALSE).

Result

The result of the function is a large integer.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Notes

• Increasing portability of applications:
  If the first argument is numeric, or if the first argument is a string and the length argument is specified, use the CAST specification instead of this function to increase the portability of your applications.

Examples

1. Using the EMPLOYEE table, select a list containing salary (SALARY) divided by education level (EDLEVEL).
   Truncate any decimal in the calculation.
   The list should also contain the values used in the calculation and employee number (EMPNO).
   The list should be in descending order of the calculated value.

      SELECT INTEGER (SALARY / EDLEVEL)
      ,      SALARY
      ,      EDLEVEL
      ,      EMPNO
      FROM   EMPLOYEE
      ORDER  BY 1 DESC

2. Using the EMPLOYEE table, select the EMPNO column in integer form for further processing in the application.

      SELECT INTEGER(EMPNO) FROM EMPLOYEE

3. Assume that the column BIRTHDATE (whose data type is DATE) has an internal value equivalent to '1964-07-20'.

       INTEGER(BIRTHDATE)

   results in the value 19 640 720.


4. Assume that the column STARTTIME (whose data type is TIME) has an internal value equivalent to '12:03:04'.

      INTEGER(STARTTIME)

   results in the value 120 304.


5. The following statement returns the value 1 of data type INTEGER.

      values INTEGER(TRUE)

6. The following statement returns the value 0 of data type INTEGER.

      values INTEGER(3>3)