CAST is a complex function that transforms one or more values from one data type into another.
.-<< , <<------------. V | >>-CAST--(----source_expression -+-- AS--DataType---------------> >--+-------------------+--+----------------------+--------------> '-CCSID--expression-' '-ENCODING--expression-' >--+--------------------+--+---------------------+--)---------->< '-FORMAT--expression-' '-DEFAULT--expression-'
In practice, you cannot specify all of the above parameters at the same time. For example, CCSID and ENCODING parameters take effect only on string-to-string conversions, while FORMAT applies only to string-numeric and string-datetime conversions (in either direction).
Not all conversions are supported; see Supported casts for a list of supported conversions.
CAST returns its first parameter (source_expression), which can contain more than one value, as the data type that is specified by its second parameter (DataType). In all cases, if the source expression is NULL, the result is NULL. If the evaluated source expression is not compatible with the target data type, or if the source expression is of the wrong format, a runtime error is generated.
The CCSID parameter is used only for conversions to or from one of the string data types. Use the CCSID parameter to specify the code page of the source or target string.
The CCSID parameter can be any expression that evaluates to a value of type INT. The expression is interpreted according to normal IBM® Integration Bus rules for CCSIDs. See Supported code pages for a list of valid values.
The DEFAULT parameter provides a method of avoiding exceptions being thrown from CAST statements by providing a last-resort value to return.
The DEFAULT parameter must be a valid ESQL expression that returns the same data type as that is specified on the DataType parameter, otherwise an exception is thrown.
The CCSID, ENCODING, and FORMAT parameters are not applied to the result of the DEFAULT parameter; the expression must, therefore, be of the correct CCSID, ENCODING, and FORMAT.
Use the FORMAT parameter for conversions between string data types and numeric or date/time data types. For conversions from string types, FORMAT defines how the source string should be parsed to fill the target data type. For conversions to string types, it defines how the data in the source expression is formatted in the target string.
FORMAT takes different types of expression for date/time and numeric conversions. However, the same FORMAT expression can be used irrespective of whether the conversion is to a string or from a string.
For more information about conversion to and from numeric data types, see Formatting and parsing numbers as strings. For more information about conversion to and from date/time data types, see Formatting and parsing dateTimes as strings.
The FORMAT parameter is equivalent to those used in many other products, such as ICU and Microsoft Excel.
DECLARE source DECIMAL 31415.92653589;
DECLARE target CHARACTER;
DECLARE pattern CHARACTER '#,##0.00';
SET target = CAST(source AS CHARACTER FORMAT pattern);
-- target is now '31,415.93'
DECLARE now CHARACTER;
SET now = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyyMMdd-HHmmss');
-- target is now '20041007-111656' (in this instance at least)
DECLARE source CHARACTER '01-02-03';
DECLARE target DATE;
DECLARE pattern CHARACTER 'dd-MM-yy';
SET target = CAST(source AS DATE FORMAT pattern);
-- target now contains Year=2003, Month=02, Day=01
DECLARE source CHARACTER '12 Jan 03, 3:45pm';
DECLARE target TIMESTAMP;
DECLARE pattern CHARACTER 'dd MMM yy, h:mma';
SET target = CAST(source AS TIMESTAMP FORMAT pattern);
-- target now contains Year=2003, Month=01, Day=03, Hour=15, Minute=45,
Seconds=58
-- (seconds taken from CURRENT_TIME since not present in input)
DECLARE source DECIMAL -54231.122;
DECLARE target CHARACTER;
DECLARE pattern CHARACTER '#,##0.00;(#,##0.00)';
SET target = CAST(source AS CHARACTER FORMAT pattern);
-- target is now '£(54,231.12)'
DECLARE source CHARACTER '16:18:30';
DECLARE target TIME;
DECLARE pattern CHARACTER 'hh:mm:ss';
SET target = CAST(source AS TIME FORMAT pattern);
-- target now contains Hour=16, Minute=18, Seconds=30
CAST(7, 6, 5 AS DATE);
CAST(7.4e0, 6.5e0, 5.6e0 AS DATE);
CAST(7.6, 6.51, 5.4 AS DATE);
CAST(9, 8, 7 AS TIME);
CAST(9.4e0, 8.6e0, 7.1234567e0 AS TIME);
CAST(9.6, 8.4, 7.7654321 AS TIME);
CAST(DATE '0001-02-03', TIME '04:05:06' AS TIMESTAMP);
CAST(2, 3, 4, 5, 6, 7.8 AS TIMESTAMP);
CAST(DATE '0002-03-04', GMTTIME '05:06:07' AS GMTTIMESTAMP);
CAST(3, 4, 5, 6, 7, 8 AS GMTTIMESTAMP);
CAST(3.1e0, 4.2e0, 5.3e0, 6.4e0, 7.5e0, 8.6789012e0 AS GMTTIMESTAMP);
CAST(3.2, 4.3, 5.4, 6.5, 7.6, 8.7890135 AS GMTTIMESTAMP);
CAST(1234 AS INTERVAL YEAR);
CAST(32, 10 AS INTERVAL YEAR TO MONTH );
CAST(33, 11 AS INTERVAL DAY TO HOUR );
CAST(34, 12 AS INTERVAL HOUR TO MINUTE);
CAST(35, 13 AS INTERVAL MINUTE TO SECOND);
CAST(36, 14, 10 AS INTERVAL DAY TO MINUTE);
CAST(37, 15, 11 AS INTERVAL HOUR TO SECOND);
CAST(38, 16, 12, 10 AS INTERVAL DAY TO SECOND);
CAST(2345.67e0 AS INTERVAL YEAR );
CAST(3456.78e1 AS INTERVAL MONTH );
CAST(4567.89e2 AS INTERVAL DAY );
CAST(5678.90e3 AS INTERVAL HOUR );
CAST(6789.01e4 AS INTERVAL MINUTE);
CAST(7890.12e5 AS INTERVAL SECOND);
CAST(7890.1234e0 AS INTERVAL SECOND);
CAST(2345.67 AS INTERVAL YEAR );
CAST(34567.8 AS INTERVAL MONTH );
CAST(456789 AS INTERVAL DAY );
CAST(5678900 AS INTERVAL HOUR );
CAST(67890100 AS INTERVAL MINUTE);
CAST(789012000 AS INTERVAL SECOND);
CAST(7890.1234 AS INTERVAL SECOND);
CAST(INTERVAL '1234' YEAR AS FLOAT);
CAST(INTERVAL '2345' MONTH AS FLOAT);
CAST(INTERVAL '3456' DAY AS FLOAT);
CAST(INTERVAL '4567' HOUR AS FLOAT);
CAST(INTERVAL '5678' MINUTE AS FLOAT);
CAST(INTERVAL '6789.01' SECOND AS FLOAT);
CAST(INTERVAL '1234' YEAR AS DECIMAL);
CAST(INTERVAL '2345' MONTH AS DECIMAL);
CAST(INTERVAL '3456' DAY AS DECIMAL);
CAST(INTERVAL '4567' HOUR AS DECIMAL);
CAST(INTERVAL '5678' MINUTE AS DECIMAL);
CAST(INTERVAL '6789.01' SECOND AS DECIMAL);
CAST(7, 6, 32 AS DATE DEFAULT DATE '1947-10-24');
CAST(2, 3, 4, 24, 6, 7.8 AS TIMESTAMP DEFAULT TIMESTAMP '1947-10-24 07:08:09');
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE LIKE '%' BEGIN
SET OutputRoot.XMLNS.Data.Date.FromIntegersInvalidCast = 'Exception thrown';
END;
DECLARE Dummy CHARACTER CAST(7, 6, 32 AS DATE);
END;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE LIKE '%' BEGIN
SET OutputRoot.XMLNS.Data.Timestamp.FromIntegersInvalidCast = 'Exception thrown';
END;
DECLARE Dummy CHARACTER CAST(2, 3, 4, 24, 6, 7.8 AS TIMESTAMP);
END;
-- Input BLOB must be 16 digits
DECLARE source BLOB X'0000000000000006';
DECLARE target INT;
SET target = CAST(source AS INT);
-- target is now base 10 integer 6
SET source = X'00000000000012C4';
SET target = CAST(source AS INT);
-- target is now base 10 integer 4804
DECLARE source INT 6;
DECLARE target BLOB;
SET target = CAST(source AS BLOB);
-- target is now x'0000000000000006'
SET source = 4804;
SET target = CAST(source AS BLOB);
-- target is now x'00000000000012C4'