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.

Table 1. Supported casts: one-to-one mappings of source to target data-type
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:
  1. If you specify either a CCSID or ENCODING clause, the whole string is written out in the code page or encoding that you requested. For example, the string "Cat" in CCSID 850 becomes the three-byte array in hexadecimal, 43,61,74.
  2. If you specify neither the CCSID nor ENCODING clause, the string must itself contain two-character hexadecimal digits of the form X'hhhhhh' or hhhhhh (where h can be any hexadecimal characters). In this case, the input string "436174" becomes the same three-byte binary array (43,61,74).

    Note that an error is generated if the input string is not of the correct format.

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.

See also Formatting and parsing dateTimes as strings.
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.

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: 
CAST(DATE '2002-10-05' AS CHARACTER)
returns
DATE '2002-10-05'

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

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

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 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 CHARACTER)
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 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 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 CHARACTER)
returns
TIME '09:24:15'

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: 
CAST(TIMESTAMP '2002-10-05 09:24:15' AS CHARACTER)
returns
TIMESTAMP '2002-10-05 09:24:15'

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.
Table 2. Supported casts: many-to-one mappings of source to target data-type
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.