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 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.

Related concepts
ESQL
ESQL data types

Related tasks
Developing ESQL

Related reference
Complex ESQL functions
CAST function
CAST specification
Implicit casts
Data types of values from external sources