Hitachi

Hitachi Advanced Database SQL Reference

8.13.3 CAST

Converts the data type of the data.

Organization of this subsection

(1) Specification format

scalar-function-CAST ::= CAST(data-to-convert AS post-conversion-data-type)
 
  data-to-convert ::= {value-expression|NULL}
  post-conversion-data-type ::= data-type

To Page Top

(2) Explanation of specification format

data-to-convert:

Specifies the data whose data type is to be converted.

Specify the data to be converted in the form of a value expression. Alternatively, specify NULL. For details about value expressions, see 7.21 Value expression.

Also, you cannot specify array data and structure data for the data to be converted.

post-conversion-data-type:

Specifies the data type after conversion. The following are examples:

  • INTEGER

    Convert to INTEGER type data.

  • DECIMAL(5,2)

    Convert to DECIMAL type data with a precision of 5 and a scaling of 2.

  • CHAR(8)

    Convert to CHAR type data with a data length of 8 bytes.

For the specification formats of each data type, see 6.2.1 List of data types.

Note that the following data types cannot be specified as the post-conversion data type.

  • VARCHAR-type data whose data length exceeds 32,000 bytes

  • Array type

  • STRUCT type

The following example illustrates the result of executing the scalar function CAST.

Example:

Convert the DECIMAL type data -12.37 to INTEGER type.

CAST(-12.37 AS INTEGER) → -12

To Page Top

(3) Rules

(a) Common rules

  1. The data type of the execution result will be the data type specified in post-conversion-data-type.

    However, there are also data types that do not become the data type of the execution result, even if the specified data type is specified as post-conversion-data-type.

    • If the NUMERIC type is specified as the post-conversion-data-type, the data type of the execution result is the DECIMAL type.

    • If the FLOAT type is specified as the post-conversion-data-type, the data type of the execution result is the DOUBLE PRECISION type.

    • If the BIGINT type is specified as the post-conversion-data-type, the data type of the execution result is the INTEGER type.#

    #

    This rule applies if the data format of the integer data type is a legacy format.

  2. If a dynamic parameter is specified by itself for data-to-convert, post-conversion-data-type will be assumed to be the data type of the dynamic parameter.

    However, there are also data types that are not assumed as dynamic parameter data types, even if the specified data type is specified as post-conversion-data-type.

    • If the NUMERIC type is specified as the post-conversion-data-type, the data type of the dynamic parameter is assumed to be the DECIMAL type.

    • If the FLOAT type is specified as the post-conversion-data-type, the data type of the dynamic parameter is assumed to be the DOUBLE PRECISION type.

    • If the BIGINT type is specified as the post-conversion-data-type, the data type of the dynamic parameter is assumed to be the INTEGER type.#

    #

    This rule applies if the data format of the integer data type is a legacy format.

  3. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

  4. If the data to be converted has a null value, or you specify NULL for data-to-convert, the execution result will be a null value.

  5. If the data to be converted is character string data with a length of 0 bytes or 0 characters, it is converted as follows:

    • When converting to CHAR type: It is converted to spaces. For example, in the case of CHAR(3), it is converted to '[Figure]'. ∆ represents a half-width space.

    • When converting to VARCHAR type: It is converted to VARCHAR type data with a length of 0 bytes or 0 characters.

    • When converting to STRING type: it is converted to STRING type data with an actual length of 0 bytes or 0 characters.

    • When converting to BINARY type: It is converted to X'00'. In the case of BINARY(3), it is converted to X'000000'.

    • When converting to VARBINARY type: It is converted to VARBINARY type data with a length of 0 bytes or 0 characters.

    • In the case of other data types, it is converted to the null value.

  6. The data types that can be converted are shown in the following table.

    Table 8‒48: Data types that can be converted

    Data type of the data to be converted

    Post-conversion data type

    Numeric data

    Character string data

    Datetime data

    Binary data

    Logical data

    UUID data

    Numeric data

    Y

    Y

    D#1

    N

    N

    N

    Character string data

    Y

    Y

    D#4

    Y

    N

    N

    Datetime data

    D#2

    Y

    D#3

    N

    N

    N

    Binary data

    N

    Y

    N

    Y

    N

    N

    Logical data

    N

    Y

    N

    N

    Y

    N

    UUID data

    N

    Y

    N

    N

    N

    Y
    Legend:

    Y: Can be converted.

    D: Some data types can be converted.

    N: Cannot be converted.

    Numeric data: BIGINT, INTEGER, SMALLINT, DECIMAL, NUMERIC, DOUBLE PRECISION, FLOAT, REAL

    Character string data: CHARACTER, VARCHAR, STRING

    Datetime data: DATE, TIMESTAMP, TIME

    Binary data: BINARY, VARBINARY

    Logical data: BOOLEAN

    UUID data: UUID

    #1

    The BIGINT or INTEGER types can be converted to the following data types:

    • DATE type

    • TIMESTAMP WITHOUT TIME ZONE type

    If the integer data type format is in a legacy format, the BIGINT, INTEGER, or SMALLINT type can be converted to the above data types.

    #2

    The DATE or TIMESTAMP WITHOUT TIME ZONE types can be converted to the following data types:

    • BIGINT type

    • INTEGER type

    If the integer data type format is in a legacy format, the DATE or TIMESTAMP WITHOUT TIME ZONE types can be converted to the following data types:

    • BIGINT type

    • INTEGER type

    • SMALLINT

    #3

    • The DATE type can be converted to the DATE or TIMESTAMP WITHOUT TIME ZONE type.

    • The TIMESTAMP WITHOUT TIME ZONE type can be converted to the DATE or TIMESTAMP WITHOUT TIME ZONE type.

    • The TIMESTAMP WITH TIME ZONE type can be converted to the TIMESTAMP WITH TIME ZONE type.

    • The TIME type can be converted to the TIME type.

    #4

    Character string data cannot be converted to TIMESTAMP WITH TIME ZONE type data.

(b) Rules for converting numeric data

■ To convert numeric data to numeric data:

Conversion of numeric data to numeric data is governed by the rules described in Storage assignment of numeric data in (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.

■ To convert character string data to numeric data:

  • Any character string data to be converted (after leading and trailing spaces are removed) must obey the rules for the description format of numeric literals. For the description format rules for numeric literals, see 6.3.2 Description format of literals.

    Examples of character string data that can be converted:

    '219', '+56', '-3547', '-11.35', '887[Figure]', 'Δ95Δ'

    Examples of character string data that cannot be converted:

    'a89', '77g9', '33Δ49'

    Legend: Δ: Single-byte space

  • If the character string data item is composed of only spaces, the null value is returned.

  • Once the character string representation of the numeric literal has been converted to a numeric value, it is converted to the post-conversion data type. At that point, it is governed by the rules described in Storage assignment of numeric data in (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.

    Example:

    CAST('11.35' AS INTEGER) → 11

    Once the character string '11.35' has been converted to the DECIMAL type numeric value 11.35, it is converted to an INTEGER type numeric value. At that point, it is governed by the rules for storage assignment of numeric data, which in this case means that the decimal part is truncated.

■ To convert datetime data to numeric data:

  • The DATE type or TIMESTAMP WITHOUT TIME ZONE type can be converted to BIGINT or INTEGER type data.

    If the integer data type format is in a legacy format, the DATE or TIMESTAMP WITHOUT TIME ZONE types can be converted to BIGINT, INTEGER, or SMALLINT type data.

  • Datetime data is converted to the cumulative number of days since January 1, year 1 (CE). In the case of January 1, year 1 (CE), the cumulative number of days is 1. In the case of January 2, year 1 (CE), the cumulative number of days is 2.

    Examples:

    CAST(DATE'0001-01-03' AS INTEGER) → 3

    CAST(TIMESTAMP'0001-01-05 11:03:58' AS INTEGER) → 5

(c) Rules for converting to character string data

The rules for converting to character string data (rules about the length of data) are shown in the following table.

Table 8‒49: Rules for converting to character string data (rules about the length of data)

Condition at the time of conversion

Rules for converting to character string data

If data of character string type or binary type is converted

If data of other types is converted

A < B

If the post-conversion data type is CHAR, it is left-aligned and padded with spaces on the right.

A = B

The conversion is performed.

A > B

The data is left-aligned and the excess portion on the right is truncated.* 1

The data cannot be converted. Conversion will result in an error.* 2
Legend:

A: Length of the source data that is to be converted to character string data

B: Data length of the post-conversion data type

#1

If truncation occurs in the middle of a multi-byte character, part of the multi-byte character is returned as the value of the execution result.

#2

If the data type of the data to be converted is DOUBLE PRECISION, FLOAT or REAL, the number of decimal places of the mantissa is truncated to fit the data length specified in post-conversion-data-type (rounding to the nearest even number), so no error is generated. However, an error will be generated if the length of the data to be converted exceeds the data length specified in post-conversion-data-type even after all the decimal places of the mantissa have been truncated.

▪ To convert INTEGER, BIGINT, SMALLINT, DECIMAL, or NUMERIC type numeric data to character string data

  • The result of converting numeric data to the format of a numeric literal is output as character string data. At that point, the results are output in the shortest format that can represent the numeric literal.

    However, conversion of DECIMAL or NUMERIC type data is performed as follows:

    • The number of digits after the decimal point equals the scaling of the data type of the numeric data, and trailing zeros are not stripped.

    • If the precision of the data type of the numeric data is greater than the scaling, the number of digits in the integer part will not be 0.

    • The decimal point is always added.

    Example: +0025.100 → '25.100'

    As shown in the example, the plus sign (+) is removed. In addition, any zeros are stripped from the beginning of the integer part.

  • If the data to be converted is less than 0, it is prefixed with a minus sign (-).

▪ To convert DOUBLE PRECISION, FLOAT, or REAL type numeric data to character string data

  • The result of converting numeric data to the format of a floating-point numeric literal is output as character string data. At that point, the results are output in the shortest format that can represent the floating-point numeric literal.

    Examples:

    +1.0000000000000000E+010 → '1E10'

    +3.2000000000000000E+001 → '3.2E1'

    +0.1000000000000000E+001 → '1E0'

    +0.0000000000000000E+000 → '0E0'

    As shown in the examples, the sign is removed from the mantissa and any trailing zeros are removed from the decimal part. Also, the plus sign (+) and leading zeros are removed from the exponent.

  • If the data to be converted is less than 0, it is prefixed with a minus sign (-).

  • Exponents that are less than 0 are prefixed with a minus sign (-).

▪ To convert datetime data to character string data

  • When datetime data is converted to character string data, it is converted to the format of the predefined output representation. When DATE type data is converted to character string data, it is converted to the format of the predefined output representation of a date. When TIME type data is converted to character string data, it is converted to the format of the predefined output representation of a time. When TIMESTAMP type data is converted to character string data, it is converted to the format of the predefined output representation of a time stamp. For details about the predefined output representations, see 6.3.3 Predefined character-string representations.

    Examples:

    CAST(DATE'2013-06-30' AS CHAR(10)) → '2013-06-30'

    CAST(DATE'0001-01-01' AS CHAR(10)) → '0001-01-01'

    CAST(TIME'05:33:48.123' AS CHAR(12)) → '05:33:48.123'

    CAST(TIMESTAMP'2013-06-30 11:03:58' AS CHAR(19)) → '2013-06-30 11:03:58'

    CAST(TIMESTAMP'2025-07-30 11:03:58+09:00' AS CHAR(25)) → '2025-07-30 11:03:58+09:00'

  • Conversions of datetime data to CHAR(n) or VARCHAR(n) must meet the following conditions:

    Data type of the data to be converted

    Condition on the post-conversion data length

    DATE

    n ≥ 10

    TIME(p)

    when p = 0

    n ≥ 8

    when p > 0

    n ≥ 9 + p

    TIMESTAMP(p) WITHOUT TIME ZONE

    when p = 0

    n ≥ 19

    when p > 0

    n ≥ 20 + p

    TIMESTAMP(p) WITH TIME ZONE

    when p = 0

    n ≥ 25

    when p > 0

    n ≥ 26 + p

    When n is less than the lengths indicated above, conversion is not possible.

  • When converting DATE type data to CHAR type, if the data length of the post-conversion data is 11 bytes or greater, the results are left-aligned and padded with spaces on the right.

    Example:

    CAST(DATE'2013-06-30' AS CHAR(15)) → '2013-06-30[Figure]'

    Legend: Δ: Single-byte space

  • When converting TIME type data with fractional seconds precision p to CHAR type, if the data length of the post-conversion data is greater than or equal to 10 + p bytes (or greater than or equal to 9 bytes when p = 0), the results are left-aligned and padded with spaces on the right.

    Example:

    CAST(TIME'11:03:58.123' AS CHAR(13)) → '11:03:58.123Δ'

    Legend: Δ: Single-byte space

  • When converting TIMESTAMP WITHOUT TIME ZONE type data with fractional seconds precision p to CHAR type, if the data length of the post-conversion data is greater than or equal to 21 + p bytes (or greater than or equal to 20 bytes when p = 0), the results are left-aligned and padded with spaces on the right.

    In addition, when converting TIMESTAMP WITH TIME ZONE type data with fractional seconds precision of p to CHAR type, if the data length of the post-conversion data is greater than or equal to 27 + p bytes (or greater than or equal to 26 bytes when p = 0), the results are left-aligned and padded with spaces on the right.

    Example:

    CAST(TIMESTAMP'2013-06-30 11:03:58' AS CHAR(20)) → '2013-06-30 11:03:58Δ'

    Legend: Δ: Single-byte space

▪ To convert binary data to character string data

  • Only the data type is converted, and the data itself (character encoding itself) is not converted.

    Example:

    CAST(X'61626364' AS CHAR(4)) ==> 'abcd'

  • If length-of-data-before-type-conversion > length-of-data-after-type-conversion, the excess portion on the right is truncated.

    Example:

    CAST(X'61626364' AS CHAR(3)) ==> 'abc'

    The underlined portion is truncated.

  • If length-of-data-before-type-conversion < length-of-data-after-type-conversion, the results are padded with half-width spaces on the right.

    Example:

    CAST(X'61626364' AS CHAR(5)) ==> 'abcd∆'

    Legend: ∆: Half-width space

▪ To convert logical data to character string data

To convert logical data to character string data, the data is converted to the format of the predefined output representation for logical data. For details about the predefined output representations, see (4) Predefined character-string representation of logical data in 6.3.3 Predefined character-string representations.

▪ To convert UUID data to character string data

To convert UUID data to character string data, the data is converted to the format of the predefined output representation for UUID data. For details about the predefined output representations, see (5) Predefined character-string representation of UUID data in 6.3.3 Predefined character-string representations.

(d) Rules for converting to datetime data

■ To convert numeric data to datetime data

  • The data is first converted to the cumulative number of days since January 1, year 1 (CE).

  • The time portion of the TIMESTAMP WITHOUT TIME ZONE type is converted to 00:00:00, and fractional seconds are filled with zeros. The following shows examples.

    Example:

    CAST(2 AS DATE) → DATE'0001-01-02'

    CAST(2 AS TIMESTAMP(3)) → TIMESTAMP'0001-01-02 00:00:00.000'

  • BIGINT type or INTEGER type data can be converted to DATE or TIMESTAMP WITHOUT TIME ZONE type data.

    If the integer data type format is in a legacy format, the BIGINT, INTEGER, or SMALLINT types can be converted to DATE or TIMESTAMP WITHOUT TIME ZONE type data.

  • INTEGER, BIGINT, and SMALLINT type data in the range 1 to 3,652,059 can be converted. Values outside this range generate an error.

■ To convert character string data to datetime data:

  • The character string data to be converted (after leading and trailing spaces are removed) can be converted to DATE type data only when it adheres to the predefined input representation format of a date. For details about the predefined input representation of a date, see (a) Predefined input representation in (1) Predefined character-string representation of dates in 6.3.3 Predefined character-string representations.

    Example:

    CAST('2014-07-22[Figure]' AS DATE) → DATE'2014-07-22'

    Examples of character string data that can be converted:

    '2014-06-30', '0001-01-02', '[Figure]2014-07-30', 'Δ2014/07/30[Figure]'

    Examples of character string data that cannot be converted:

    '2013Δ06Δ30', '2013.06.30'

    Legend: Δ: Single-byte space

  • The character string data to be converted (after leading and trailing spaces are removed) can be converted to TIME type data only when it adheres to the predefined input representation format of a time. For details about the predefined input representation of a time, see (a) Predefined input representation in (2) Predefined character-string representation of times in 6.3.3 Predefined character-string representations.

    Example:

    CAST('Δ19:46:23.123456' AS TIME(6)) → TIME'19:46:23.123456'

    Examples of character string data that can be converted:

    '18:05:22', '10:21:44.123', '[Figure]10:21:44.123456Δ'

    Examples of character string data that cannot be converted:

    '18Δ05Δ22', '10:21:44Δ123456'

    Legend: Δ: Single-byte space

  • The character string data to be converted (after leading and trailing spaces are removed) can be converted to TIMESTAMP WITHOUT TIME ZONE type data only when it adheres to the predefined input representation format of a time stamp. For details about the predefined input representation of a time stamp, see (a) Predefined input representation in (3) Predefined character-string representation of time stamps in 6.3.3 Predefined character-string representations.

    Example:

    CAST('2014/08/02 11:03:58.123456Δ' AS TIMESTAMP(6)) → TIMESTAMP'2014-08-02 11:03:58.123456'

    Examples of character string data that can be converted:

    '2014-06-30 11:03:58', '2014/07/30 11:03:58.123', 'Δ2014/07/30 11:03:58.123456789[Figure]'

    Examples of character string data that cannot be converted:

    '2014-06-30 11-03-58', '2014/07/30 11:03:58:123456'

    Legend: Δ: Single-byte space

  • If the number of digits in the fractional seconds of the character string data to be converted is greater than the number of digits in the fractional seconds of post-conversion-data-type, the fractional seconds beyond the number of digits in the fractional seconds of post-conversion-data-type are truncated.

    Example:

    CAST('19:46:23.123456' AS TIME(3)) → TIME'19:46:23.123'

  • If the number of digits in the fractional seconds of the character string data to be converted is less than the number of digits in the fractional seconds of post-conversion-data-type, the fractional seconds are padded with zeros as necessary.

    Example:

    CAST('2014-08-02 11:03:58.123' AS TIMESTAMP(9)) → TIMESTAMP'2014-08-02 11:03:58.123000000'

  • If the character string data item is composed of only spaces, the null value is returned.

■ To convert datetime data to datetime data:

The conversion rules for converting datetime data to datetime data are given in the following table.

Table 8‒50: Conversion rules for converting datetime data to datetime data

Data type of the data to be converted

Specified post-conversion data type

Conversion rules

DATE

DATE

No conversion is performed.

TIMESTAMP(p2)

  • The time part is converted to 00:00:00.

  • The fractional seconds are padded with zeros.

TIME(p1)

TIME(p2)

  • When p1 = p2

    No conversion is performed.

  • When p1 > p2

    The fractional seconds beyond p2 are truncated.

  • When p1 < p2

    The missing fractional seconds are padded with zeros.

TIMESTAMP(p1)

DATE

Only the date part is converted.

TIMESTAMP(p2)

  • When p1 = p2

    No conversion is performed.

  • When p1 > p2

    The fractional seconds beyond p2 are truncated.

  • When p1 < p2

    The missing fractional seconds are padded with zeros.
Legend:

p1, p2: Fractional seconds precision

(e) Rules for converting to binary data

■ To convert character string data to binary data

  • Only the data type is converted, and the data itself (character encoding itself) is not converted.

    Example:

    CAST('abcd' AS BINARY(4)) ==> X'61626364'

  • If length-of-data-before-type-conversion > length-of-data-after-type-conversion, the excess portion on the right is truncated.

    Example:

    CAST('abcd' AS BINARY(3)) ==> X'616263'

    The underlined portion is truncated.

    If truncation occurs in the middle of a multi-byte character, part of the multi-byte character is returned as the value of the execution result.

  • If length-of-data-before-type-conversion < length-of-data-after-type-conversion, the results are padded with X'00' on the right.

    Example:

    CAST('abcd' AS BINARY(5)) ==> X'6162636400'

■ To convert binary data to binary data

  • If length-of-data-before-type-conversion > length-of-data-after-type-conversion, the excess portion on the right is truncated.

    Example:

    CAST(X'61626364' AS BINARY(3)) ==> X'616263'

    The underlined portion is truncated.

    If truncation occurs in the middle of a multi-byte character, part of the multi-byte character is returned as the value of the execution result.

  • If length-of-data-before-type-conversion < length-of-data-after-type-conversion, the results are padded with X'00' on the right.

    Example:

    CAST(X'61626364' AS BINARY(5)) ==> X'6162636400'

(f) Rules for converting to logical data

Logical data can only be converted to logical data. In this case, no conversion processing is performed. The data before conversion remains unchanged as the data after conversion.

(g) Rules for converting to UUID data

UUID data can only be converted to UUID data. In this case, no conversion processing is performed. The data before conversion remains unchanged as the data after conversion.

To Page Top

(4) Example

Example:

Convert the data in column C2 in table T1 from TIMESTAMP WITHOUT TIME ZONE type to DATE type and retrieve the rows where column C2 is July 21, 2013.

SELECT * FROM "T1"
    WHERE CAST("C2" AS DATE)=DATE'2013-07-21'

[Figure]

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.