Type conversions

Data types may be converted from one form to another either explicitly or implicitly. Implicit conversions automatically occur in criteria and expressions to ease development. Explicit datatype conversions require the use of the CONVERT function or CAST keyword.

Type conversion considerations

  • Any type may be implicitly converted to the OBJECT type.

  • The OBJECT type can be explicitly converted to any other type.

  • The NULL value can be converted to any type.

  • Any valid implicit conversion is also a valid explicit conversion.

  • In scenarios where literal values would normally require explicit conversions, you can apply implicit conversions if no loss of information occurs.

  • If widenComparisonToString is false (the default), Teiid raises an exception if it detects that an explicit conversion cannot be applied implicitly in criteria.

  • If widenComparisonToString is true, then depending upon the comparison, a widening conversion is applied or the criteria are treated as false. For more information about widenComparisonToString, see System properties in the Administrator’s Guide.

    SELECT * FROM my.table WHERE created_by = 'not a date'

    If widenComparisonToString is false, and created_by is a date, not a date cannot be converted to a date value, and an exception results.

  • Explicit conversions that are not allowed between two types will result in an exception before execution. Allowed explicit conversions can still fail during processing if the runtime values are not actually convertible.

Warning
 The Teiid conversions of float/double/bigdecimal/timestamp to string rely on the JDBC/Java defined output formats. Pushdown behavior attempts to mimic these results, but can vary depending upon the actual source type and conversion logic. It is best not to assume use of the string form in criteria or other places where variations might lead to different results.
Table 1. Type conversions
Source type Valid implicit target types Valid explicit target types

string

clob

char, boolean, byte, short, integer, long, biginteger, float, double, bigdecimal, xml [1]

char

string

boolean

string, byte, short, integer, long, biginteger, float, double, bigdecimal

byte

string, short, integer, long, biginteger, float, double, bigdecimal

boolean

short

string, integer, long, biginteger, float, double, bigdecimal

boolean, byte

integer

string, long, biginteger, double, bigdecimal

boolean, byte, short, float

long

string, biginteger, bigdecimal, float [2], double [2]

boolean, byte, short, integer, float, double

biginteger

string, bigdecimal float [2], double [2]

boolean, byte, short, integer, long, float, double

bigdecimal

string, float [2], double [2]

boolean, byte, short, integer, long, biginteger, float, double

float

string, bigdecimal, double

boolean, byte, short, integer, long, biginteger

double

string, bigdecimal, float [2]

boolean, byte, short, integer, long, biginteger, float

date

string, timestamp

time

string, timestamp

timestamp

string

date, time

clob

string

json

clob

string

xml

string [3]

geography

geometry
1. string to xml is equivalent to XMLPARSE(DOCUMENT exp). For more information, see XMLPARSE in XML functions.
2. Implicit conversion to float/double only occurs for literal values.
3. xml to string is equivalent to XMLSERIALIZE(exp AS STRING). For more information, see XMLSERIALIZE in XML functions.

results matching ""

    No results matching ""