Supported casts
This topic lists the CASTs that are supported between combinations of data-types.
A CAST is not supported between every combination of data-types. Those that are supported are listed in the following table, along with the effect of the CAST.
When casting, there can be a one-to-one or a many-to-one mapping between the source data-type and the target data-type. An example of a one-to-one mapping is where the source data-type is a single integer and the target data-type a single float. An example of a many-to-one mapping is where the source data consists of three integers that are converted to a single date. Table 1 lists the supported one-to-one casts. Table 2 lists the supported many-to-one casts.
See ESQL data types overview for
information about
, precision
scale
,
and interval qualifier
.
Source data-type | Target data-type | Effect |
---|---|---|
BIT | BIT | The result is the same as the input. |
BIT | BLOB | The bit array is converted to a byte array with a maximum of 263 elements. An error is reported if the source is not of a suitable length to produce a BLOB (that is a multiple of 8). |
BIT | CHARACTER | The result is a string conforming to the definition
of a bit string literal whose interpreted value is the same as the
source value. The resulting string has the form B'bbbbbb' (where b
is either 0 or 1). If you specify either a CCSID or ENCODING clause, the bit array is assumed to be characters in the specified CCSID and encoding, and is code-page converted into the character return value. If you specify only a CCSID, big endian encoding is assumed. If you specify only an encoding, a CCSID of 1208 is assumed. This function reports conversion errors if the code page or encoding are unknown, the data supplied is not an integral number of characters of the code page, or the data contains characters that are not valid in the given code page. |
BIT | INTEGER | The bit array has a maximum of 263 elements and is converted to an integer. An error is reported if the source is not of the correct length to match an integer. |
BLOB | BIT | The given byte array is converted to a bit array with a maximum of 263 elements. |
BLOB | BLOB | The result is the same as the input. |
BLOB | CHARACTER | The result is a string conforming to the definition
of a binary string literal whose interpreted value is the same as
the source value. The resulting string has the form X'hhhh' (where
h is any hexadecimal character). If you specify either a CCSID or ENCODING clause, the byte array is assumed to be characters in the specified CCSID and encoding, and is code-page converted into the character return value. If you specify only a CCSID, big endian encoding is assumed. If you specify only an encoding, a CCSID of 1208 is assumed. This function reports conversion errors if the code page or encoding are unknown, the data supplied is not an integral number of characters of the code page, or the data contains characters that are not valid in the given code page. |
BLOB | INTEGER | The byte array has a maximum of 263 elements and is converted to an integer. An error is reported if the source is not of the correct length to match an integer. |
BOOLEAN | BOOLEAN | The result is the same as the input. |
BOOLEAN | CHARACTER | If the source value is TRUE, the result is the character string TRUE. If the source value is FALSE, the result is the character string FALSE. Because the UNKNOWN Boolean value is the same as the NULL value for Booleans, the result is NULL if the source value is UNKNOWN. |
CHARACTER | BIT | The character string must conform to the rules
for a bit string literal or for the contents of the bit string literal.
That is, the character string can be of the form B'bbbbbbb' or bbbbbb
(where b' can be either 0 or 1). If you specify either a CCSID or ENCODING clause, the character string is converted into the specified CCSID and encoding and placed without further conversion into the bit array return value. If you specify only a CCSID, big endian encoding is assumed. If you specify only an encoding, a CCSID of 1208 is assumed. This function reports conversion errors if the code page or encoding are unknown or the data contains Unicode characters that cannot be converted to the given code page. |
CHARACTER | BLOB | This cast can work in two ways:
If you specify only a CCSID, big endian encoding is assumed. If you specify only an encoding, a CCSID of 1208 is assumed. This function reports conversion errors if the code page or encoding are unknown or the data contains Unicode characters that cannot be converted to the given code page. |
CHARACTER | BOOLEAN | The character string is interpreted in the same way as a Boolean literal. That is, the character string must be one of the strings TRUE, FALSE, or UNKNOWN (in any case combination). |
CHARACTER | CHARACTER | The result is the same as the input. |
CHARACTER | DATE | If a FORMAT clause is not specified, the character string must conform to the rules for a date literal or the date string. That is, the character string can be either DATE '2002-10-05' or 2002-10-05. |
CHARACTER | DECIMAL | The character string is interpreted in the same
way as an exact numeric literal to form a temporary decimal result
with a scale and defined
by the format of the string. This is converted into a decimal of
the specified precision and scale ,
with a runtime error being generated if the conversion results in
loss of significant digits. If you do not specify the The behavior changes if the FORMAT clause is specified. See also Formatting and parsing numbers as strings. |
CHARACTER | FLOAT | The character string is interpreted in the same
way as a floating point literal. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing numbers as strings. |
CHARACTER | GMTTIME | The character string must conform to the rules
for a GMT time literal or the time string. That is, the character
string can be either GMTTIME '09:24:15' or 09:24:15. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
CHARACTER | GMTTIMESTAMP | The character string must conform to the rules
for a GMT timestamp literal or the timestamp string. That is, the
character string can be either GMTTIMESTAMP '2002-10-05 09:24:15'
or 2002-10-05 09:24:15. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
CHARACTER | INTEGER | The character string is interpreted in the same
way as an integer literal. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing numbers as strings. |
CHARACTER | INTERVAL | The character string must conform to the rules
for an interval literal with the same interval qualifier as
specified in the CAST function, or it must conform to the rules for
an interval string that apply for the specified interval qualifier . |
CHARACTER | TIME | The character string must conform to the rules
for a time literal or for the time string. That is, the character
string can be either TIME '09:24:15' or 09:24:15. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
CHARACTER | TIMESTAMP | The character string must conform to the rules
for a timestamp literal or for the timestamp string. That is, the
character string can be either TIMESTAMP '2002-10-05 09:24:15' or
2002-10-05 09:24:15. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
DATE | CHARACTER | The result is a string conforming to the definition
of a date literal, whose interpreted value is the same as the source
date value. For example:
returns
The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
DATE | DATE | The result is the same as the input. |
DATE | GMTTIMESTAMP | The result is a value whose date fields are taken from the source date value, and whose time fields are taken from the current GMT time. |
DATE | TIMESTAMP | The result is a value whose date fields are taken from the source date value, and whose time fields are taken from the current time. |
DECIMAL | CHARACTER | The result is the shortest character string
that conforms to the definition of an exact numeric literal and whose
interpreted value is the value of the decimal. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing numbers as strings. |
DECIMAL | DECIMAL | The value is converted to the specified |
DECIMAL | FLOAT | The number is converted, with rounding if necessary. |
DECIMAL | INTEGER | The value is rounded and converted into an integer, with a runtime error being generated if the conversion results in loss of significant digits. |
DECIMAL | INTERVAL | If the interval qualifier specified
has only one field, the result is an interval with that qualifier
with the field equal to the value of the exact numeric. Otherwise
a runtime error is generated. |
FLOAT | CHARACTER | The result is the shortest character string that
conforms to the definition of an approximate numeric literal and whose
mantissa consists of a single digit that is not 0, followed by a period
and an unsigned integer, and whose interpreted value is the value
of the float. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing numbers as strings. When you CAST a FLOAT to a DECIMAL or CHARACTER, either implicitly or explicitly, the FLOAT can be rounded to a maximum precision of 15 digits. |
FLOAT | FLOAT | The result is the same as the input. |
FLOAT | DECIMAL | The value is rounded and converted into a
decimal of the specified When you CAST a FLOAT to a DECIMAL or CHARACTER, either implicitly or explicitly, the FLOAT can be rounded to a maximum precision of 15 digits. |
FLOAT | INTEGER | The value is rounded and converted into an integer, with a runtime error being generated if the conversion results in loss of significant digits. |
FLOAT | INTERVAL | If the specified interval qualifier has only
one field, the result is an interval with that qualifier with the
field equal to the value of the numeric. Otherwise a runtime error
is generated. |
GMTTIME | CHARACTER | The result is a string conforming to the definition
of a GMTTIME literal whose interpreted value is the same as the source
value. The resulting string has the form GMTTIME 'hh:mm:ss'. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
GMTTIME | GMTTIME | The result is the same as the input. |
GMTTIME | TIME | The resulting value is the source value plus the local time zone displacement (as returned by LOCAL_TIMEZONE). The hours field is calculated modulo 24. |
GMTTIME | GMTTIMESTAMP | The result is a value whose date fields are taken from the current date, and whose time fields are taken from the source GMT time. |
GMTTIME | TIMESTAMP | The result is a value whose date fields are taken from the current date, and whose time fields are taken from the source GMT time, plus the local time zone displacement (as returned by LOCAL_TIMEZONE). |
GMTTIMESTAMP | CHARACTER | The result is a string conforming to the definition
of a GMTTIMESTAMP literal whose interpreted value is the same as the
source value. The resulting string has the form GMTTIMESTAMP 'yyyy-mm-dd
hh:mm:ss'. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
GMTTIMESTAMP | DATE | The result is a value whose fields consist of the date fields of the source GMTTIMESTAMP value. |
GMTTIMESTAMP | GMTTIME | The result is a value whose fields consist of the time fields of the source GMTTIMESTAMP value. |
GMTTIMESTAMP | TIME | The result is a value whose time fields are taken from the source GMTTIMESTAMP value, plus the local time zone displacement (as returned by LOCAL_TIMEZONE). The hours field is calculated modulo 24. |
GMTTIMESTAMP | GMTTIMESTAMP | The result is the same as the input. |
GMTTIMESTAMP | TIMESTAMP | The resulting value is source value plus the local time zone displacement (as returned by LOCAL_TIMEZONE). |
INTEGER | BIT | The given integer is converted to a bit array with a maximum of 263 elements. |
INTEGER | BLOB | The given integer is converted to a byte array with a maximum of 263 elements. |
INTEGER | CHARACTER | The result is the shortest character string
that conforms to the definition of an exact numeric literal and whose
interpreted value is the value of the integer. The behavior changes if the FORMAT clause is specified. See also Formatting and parsing numbers as strings. |
INTEGER | FLOAT | The number is converted, with rounding if necessary. |
INTEGER | INTEGER | The result is the same as the input. |
INTEGER | DECIMAL | The value is converted into a decimal of
the specified |
INTEGER | INTERVAL | If the interval qualifier specified
has only one field, the result is an interval with that qualifier
with the field equal to the value of the exact numeric. Otherwise
a runtime error is generated. |
INTERVAL | CHARACTER | The result is a string conforming to the definition
of an INTERVAL literal, whose interpreted value is the same as the
source interval value. For example:
returns
|
INTERVAL | DECIMAL | If the interval value has a qualifier that
has only one field, the result is a decimal of the specified |
INTERVAL | FLOAT | If the interval value has a qualifier that has only one field, the result is a float with that value. If the interval has a qualifier with more than one field, such as YEAR TO MONTH, a runtime error is generated. |
INTERVAL | INTEGER | If the interval value has a qualifier that has only one field, the result is an integer with that value. If the interval has a qualifier with more than one field, such as YEAR TO MONTH, a runtime error is generated. |
INTERVAL | INTERVAL | The result is the same as the input. Year-month
intervals can be converted only to year-month intervals, and day-second
intervals only to day-second intervals. The source interval is converted
into a scalar in units of the least significant field of the target |
TIME | CHARACTER | The result is a string conforming to the definition
of a TIME literal, whose interpreted value is the same as the source
time value. For example:
returns
The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
TIME | GMTTIME | The result value is the source value minus the local time zone displacement (as returned by LOCAL_TIMEZONE). The hours field is calculated modulo 24. |
TIME | GMTTIMESTAMP | The result is a value whose date fields are taken from the current date, and whose time fields are taken from the source GMT time, minus the local time zone displacement (as returned by LOCAL_TIMEZONE). |
TIME | TIME | The result is the same as the input. |
TIME | TIMESTAMP | The result is a value whose date fields are taken from the current date, and whose time fields are taken from the source time value. |
TIMESTAMP | CHARACTER | The result is a string conforming to the definition
of a TIMESTAMP literal, whose interpreted value is the same as the
source timestamp value. For example:
returns
The behavior changes if the FORMAT clause is specified. See also Formatting and parsing dateTimes as strings. |
TIMESTAMP | DATE | The result is a value whose fields consist of the date fields of the source timestamp value. |
TIMESTAMP | GMTTIME | The result is a value whose time fields are taken from the source TIMESTAMP value, minus the local time zone displacement (as returned by LOCAL_TIMEZONE). The hours field is calculated modulo 24. |
TIMESTAMP | GMTTIMESTAMP | The resulting value is the source value minus the local time zone displacement (as returned by LOCAL_TIMEZONE). |
TIMESTAMP | TIME | The result is a value whose fields consist of the time fields of the source timestamp value. |
TIMESTAMP | TIMESTAMP | The result is the same as the input. |
Source data-type | Target data-type | Effect |
---|---|---|
Numeric, Numeric, Numeric | DATE | Creates a DATE value from the numerics in the order year, month, and day. Non-integer values are rounded. |
Numeric, Numeric, Numeric | TIME | Creates a TIME value from the numerics in the order hours, minutes, and seconds. Non-integer values for hours and minutes are rounded. |
Numeric, Numeric, Numeric | GMTIME | Creates a GMTTIME value from the numerics in the order of hours, minutes, and seconds. Non-integer values for hours and minutes are rounded. |
Numeric, Numeric, Numeric, Numeric, Numeric, Numeric | TIMESTAMP | Creates a TIMESTAMP value from the numerics in the order years, months, days, hours, minutes, and seconds. Non-integer values for years, months, days, hours, and minutes are rounded. |
Numeric, Numeric, Numeric, Numeric, Numeric, Numeric | GMTTIMESTAMP | Creates a GMTIMESTAMP value from the numerics in the order years, months, days, hours, minutes, and seconds. Non-integer values for years, months, days, hours, and minutes are rounded. |
DATE, TIME | TIMESTAMP | The result is a TIMESTAMP value with the given DATE and TIME. |
DATE, GMTTIME | GMTIMESTAMP | The result is a GMTTIMESTAMP value with the given DATE and GMTTIME. |
Numeric, Numeric | INTERVAL YEAR TO MONTH | The result is an INTERVAL with the first source as years and the second as months. Non-integer values are rounded. |
Numeric, Numeric | INTERVAL HOUR TO MINUTE | The result is an INTERVAL with the first source as hours and the second as minutes. Non-integer values are rounded. |
Numeric, Numeric, Numeric | INTERVAL HOUR TO SECOND | The result is an INTERVAL with the sources as hours, minutes, and seconds, respectively. Non-integer values for hours and minutes are rounded. |
Numeric, Numeric | INTERVAL MINUTE TO SECOND | The result is an INTERVAL with the sources as minutes and seconds, respectively. Non-integer values for minutes are rounded. |
Numeric, Numeric | INTERVAL DAY TO HOUR | The result is an INTERVAL with the sources as days and hours, respectively. Non-integer values are rounded. |
Numeric, Numeric, Numeric | INTERVAL DAY TO MINUTE | The result is an INTERVAL with the sources as days, hours, and minutes, respectively. Non-integer values are rounded. |
Numeric, Numeric, Numeric, Numeric | INTERVAL DAY TO SECOND | The result is an INTERVAL with the sources as days, hours, minutes, and seconds, respectively. Non-integer values for days, hours, and minutes are rounded. |
Numeric | INTERVAL YEAR | The result is an INTERVAL with the source as years, rounded if necessary. |
Numeric | INTERVAL MONTH | The result is an INTERVAL with the source as months, rounded if necessary. |
Numeric | INTERVAL DAY | The result is an INTERVAL with the source as days, rounded if necessary. |
Numeric | INTERVAL HOUR | The result is an INTERVAL with the source as hours, rounded if necessary. |
Numeric | INTERVAL MINUTE | The result is an INTERVAL with the source as minutes, rounded if necessary. |
Numeric | INTERVAL SECOND | The result is an INTERVAL with the source as seconds. |