Implicit Data Type Conversion

TimesTen converts a value from one data type to another when such a conversion is sensible.

Table 1-6 and Table 1-7 use a matrix to illustrate TimesTen implicit data type conversions. YES in the cell indicates the conversion is supported. NO in the cell indicates the conversion is not supported. The rules for implicit conversion follow the table.

Table 1-6 Implicit data type conversion

Blank CHAR VARCHAR2 NCHAR NVARCHAR2 DATE TT_DATE TIMESTAMP TT_TIMESTAMP

CHAR

n/a

YES

YES

YES

YES

YES

YES

YES

VARCHAR2

YES

n/a

YES

YES

YES

YES

YES

YES

NCHAR

YES

YES

n/a

YES

YES

YES

YES

YES

NVARCHAR2

YES

YES

YES

n/a

YES

YES

YES

YES

DATE

YES

YES

YES

YES

n/a

YES

YES

YES

TT_DATE

YES

YES

YES

YES

YES

n/a

YES

YES

TIMESTAMP

YES

YES

YES

YES

YES

YES

n/a

YES

TT_TIMESTAMP

YES

YES

YES

YES

YES

YES

YES

n/a

NUMERIC

YES

YES

YES

YES

NO

NO

NO

NO

BLOB

NO

NO

NO

NO

NO

NO

NO

NO

CLOB

YES

YES

YES

YES

NO

NO

NO

NO

NCLOB

YES

YES

YES

YES

NO

NO

NO

NO

BINARY/ VARBINARY

YES

YES

YES

YES

NO

NO

NO

NO

ROWID

YES

YES

YES

YES

NO

NO

NO

NO

Table 1-7 Implicit data type conversion (continuation of preceding table)

Blank NUMERIC BLOB CLOB NCLOB BINARY/ VARBINARY ROWID

CHAR

YES

YES

YES

YES

YES

YES

VARCHAR2

YES

YES

YES

YES

YES

YES

NCHAR

YES

YES

YES

YES

YES

YES

NVARCHAR2

YES

YES

YES

YES

YES

YES

DATE

NO

NO

NO

NO

NO

NO

TT_DATE

NO

NO

NO

NO

NO

NO

TIMESTAMP

NO

NO

NO

NO

NO

NO

TT_TIMESTAMP

NO

NO

NO

NO

NO

NO

NUMERIC

n/a

NO

NO

NO

NO

NO

BLOB

NO

n/a

NO

NO

YES

NO

CLOB

NO

NO

n/a

YES

NO

NO

NCLOB

NO

NO

YES

n/a

NO

NO

BINARY/ VARBINARY

NO

YES

YES

YES

n/a

NO

ROWID

NO

NO

NO

NO

NO

n/a

The following rules apply:

  • During arithmetic operations on and comparisons between character and non-character data types, TimesTen converts from any character data type to a numeric or datetime data type as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, TimesTen converts to a NUMBER.

  • During arithmetic operations, floating point values INF and NAN are not supported when converting character values to numeric values.

  • During concatenation operations, TimesTen converts non-character data types to CHAR, NCHAR, VARCHAR2, or NVARCHAR2 depending on the other operand.

  • When comparing a character value with a numeric value, TimesTen converts the character data to a numeric value.

  • When comparing a character value with a datetime value, TimesTen converts the character data to a datetime value.

  • During conversion from a timestamp value to a DATE value, the fractional seconds portion of the timestamp value is truncated.

  • Conversions from BINARY_FLOAT to BINARY_DOUBLE are exact.

  • Conversions from BINARY_DOUBLE to BINARY_FLOAT are inexact if the BINARY_DOUBLE value uses more bits of precision that supported by the BINARY_FLOAT.

  • Conversions between either character values or exact numeric values (TT_TINYINT, TT_SMALLINT, TT_INTEGER, TT_BIGINT, NUMBER) and floating-point values (BINARY_FLOAT, BINARY_DOUBLE) can be inexact because the character values and the exact numeric values use decimal precision whereas the floating-point numbers use binary precision.

  • When manipulating numeric values, TimesTen usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.

  • When making assignments, TimesTen converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.

  • When you use a SQL function or operator with an argument of a data type other than the one it accepts, TimesTen converts the argument to the accepted data type as long as TimesTen supports the implicit conversion.

  • During INSERT, INSERT... SELECT, and UPDATE operations, TimesTen converts the value to the data type of the affected column.

  • Implicit and explicit CHAR/VARCHAR2 <-> NCHAR/NVARCHAR2 conversions are supported. An example of implicit conversion: 

    Command> CREATE TABLE convdemo (c1 CHAR (10), x1 TT_INTEGER);
Command> CREATE TABLE convdemo2 (c1 NCHAR (10), x2 TT_INTEGER);
Command> INSERT INTO convdemo VALUES ('ABC', 10);
1 row inserted.
Command> INSERT INTO convdemo VALUES ('def', 100);
1 row inserted.
Command> INSERT INTO convdemo2 SELECT * FROM convdemo;
2 rows inserted.
Command> SELECT x1,x2,convdemo.c1, convdemo2.c1 
         FROM convdemo, convdemo2 
         WHERE Convdemo.c1 = convdemo2.c1;
X1, X2, C1, C1
< 10, 10, ABC       , ABC        >
< 100, 100, def       , def        >
2 rows found.