CAST function

Source expression

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.

CCSID

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.

DataType

DEFAULT

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.

ENCODING

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.

Example 1. Formatted CAST from DECIMAL to CHARACTER

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'

Example 2. Formatted CAST from DATE to CHARACTER

DECLARE now CHARACTER;
SET now = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyyMMdd-HHmmss');
-- target is now '20041007-111656' (in this instance at least)

Example 3. Formatted CAST from CHARACTER to DATE

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

Example 4. Formatted CAST from CHARACTER to TIMESTAMP

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)

Example 5. Formatted CAST from DECIMAL to CHARACTER, with negative pattern

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)'

Example 6. Formatted CAST from CHARACTER to TIME

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

Example 7. CASTs from the numeric types to DATE

CAST(7, 6, 5 AS DATE);
CAST(7.4e0, 6.5e0, 5.6e0 AS DATE);
CAST(7.6, 6.51, 5.4 AS DATE);

Example 8. CASTs from the numeric types to TIME

CAST(9, 8, 7 AS TIME);
CAST(9.4e0, 8.6e0, 7.1234567e0 AS TIME);
CAST(9.6, 8.4, 7.7654321 AS TIME);

Example 9. CASTs to TIMESTAMP

CAST(DATE '0001-02-03', TIME '04:05:06' AS TIMESTAMP);
CAST(2, 3, 4, 5, 6, 7.8 AS TIMESTAMP);

Example 10. CASTs to GMTTIMESTAMP

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);

Example 11. CASTs to INTERVAL from INTEGER

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);

Example 12. CASTs to INTERVAL from FLOAT

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);

Example 13. CASTs to INTERVAL from DECIMAL

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);

Example 14. CASTs to FLOAT from INTERVAL

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);

Example 15. CASTs DECIMAL from INTERVAL

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);

Example 16. A ternary cast that fails and results in the substitution of a default value

CAST(7, 6, 32 AS DATE DEFAULT DATE '1947-10-24');

Example 17. A sexternary cast that fails and results in the substitution of a default value

CAST(2, 3, 4, 24, 6, 7.8 AS TIMESTAMP DEFAULT TIMESTAMP '1947-10-24 07:08:09');

Example 18. A ternary cast that fails and throws an exception

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;

Example 19. A sexternary cast that fails and throws an exception

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;

Example 20. Cast BLOB to INTEGER

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

Example 21. Cast INTEGER to BLOB

   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'