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 below, along with the effect of the CAST.
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 the NULL character string value 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 | The character string must conform to the rules for a
binary string literal or for the contents of the binary string literal. That
is, the character string can be of the form X'hhhhhh' or hhhhhh (where
h can be any hexadecimal characters). 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 byte 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 | 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 | 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
precision 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 precision and scale, the precision and scale of the result are the minimum necessary to hold the given value. |
CHARACTER | FLOAT | The character string is interpreted in the same way as a floating point literal. |
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. |
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. |
CHARACTER | INTEGER | The character string is interpreted in the same way as an integer literal. |
CHARACTER | INTERVAL | The character string must conform to the rules for an interval literal with the same interval qualifier as specified in the CAST specification, 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. |
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. |
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:
CAST(DATE '2002-10-05' AS CHAR)returns DATE '2002-10-05' |
DATE | DATE | The result is the same as the input. |
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. |
DECIMAL | DECIMAL | The value is converted to 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 precision and scale, the value, precision and scale are preserved; it is a NOOP (no operation). |
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. |
FLOAT | FLOAT | The result is the same as the input. |
FLOAT | DECIMAL | The value is rounded and 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 precision and scale, the precision and scale of the result are the minimum necessary to hold the given value. |
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. |
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'. |
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. |
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'. |
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. |
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 precision and scale, with a runtime error being generated if the conversion results in loss of significant digits. If you do not specify the precision and scale, the precision and scale of the result are the minimum necessary to hold the given value. |
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:
CAST(INTERVAL '4' YEARS AS CHAR)returns INTERVAL '4' YEARS |
INTERVAL | DECIMAL | If the interval value has a qualifier that has only one field, the result is a decimal of the specified precision and scale with that value, with a runtime error being generated if the conversion results in loss of significant digits. If the interval has a qualifier with more than one field, such as YEAR TO MONTH, a runtime error is generated. If you do not specify the precision and scale, the precision and scale of the result are the minimum necessary to hold the given value. |
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 interval qualifier. This value is normalized into an interval with the target interval qualifier. For example, to convert an interval that has the qualifier MINUTE TO SECOND into an interval with the qualifier DAY TO HOUR, the source value is converted into a scalar in units of hours, and this value is normalized into an interval with qualifier DAY TO HOUR. |
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:
CAST(TIME '09:24:15' AS CHAR)returns TIME '09:24:15' |
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 | 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:
CAST(TIMESTAMP '2002-10-05 09:24:15' AS CHAR)returns TIMESTAMP '2002-10-05 09:24:15' |
TIMESTAMP | DATE | The result is a value whose fields consist of the date fields of the source timestamp value. |
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. |
See ESQL data types for information about precision, scale, and interval qualifier.
Notices |
Trademarks |
Downloads |
Library |
Support |
Feedback
![]() ![]() |
ak05680_ |