8.12.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
(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.20 Value expression.
- 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 you cannot specify a VARCHAR type whose data length exceeds 32,000 bytes for the post-conversion-data 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
(3) Rules
(a) Common rules
-
The data type of the execution result will be the data type specified in post-conversion-data-type.
-
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.
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
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.
-
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]](GRAPHICS/F0000080.GIF)
'. ∆ 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 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.
-
-
The data types that can be converted are shown in the following table.
Table 8‒45: Data types that can be converted Data type of the data to be converted
Post-conversion data type
INTEGER,
SMALLINT
DECIMAL,
DOUBLE PRECISION
CHAR,
VARCHAR
DATE, TIMESTAMP
TIME
BINARY,
VARBINARY
INTEGER,
SMALLINT
Y
Y
Y
Y
N
N
DECIMAL,
DOUBLE PRECISION
Y
Y
Y
N
N
N
CHAR,
VARCHAR
Y
Y
Y
Y
Y
Y
DATE,
TIMESTAMP
Y
N
Y
Y
N
N
TIME
N
N
Y
N
Y
N
BINARY,
VARBINARY
N
N
Y
N
N
Y
- Legend:
-
Y: Can be converted.
N: Cannot be converted.
(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]](GRAPHICS/F0000079.GIF)
', 'Δ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:
-
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.
|
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, 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, SMALLINT, or DECIMAL 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 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 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'
-
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)
when p = 0
n ≥ 19
when p > 0
n ≥ 20 + 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]](GRAPHICS/F0000287.GIF)
'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 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.
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
-
(d) Rules for converting to datetime data
- ■ To convert INTEGER or SMALLINT type 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 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'
-
INTEGER 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
' AS DATE) → DATE'2014-07-22'Examples of character string data that can be converted:
'2014-06-30', '0001-01-02', '
2014-07-30', 'Δ2014/07/30'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', '
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 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
'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‒47: 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'
-
(4) Example
- Example:
-
Convert the data in column C2 in table T1 from TIMESTAMP 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]](GRAPHICS/F0000288.GIF)