A CAST specification converts the data in a value expression into a specified data type.
CAST-specification::= CAST ([value-expression | NULL] AS data-type) |
Table 2-90 Data type convertibility between the result of the value expression and AS data type (1/2)
Data type of result of value expression | AS data type | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
Numeric data | Character string data (any character set) | National character data | Mixed character data | |||||||
Exact numeric | Approximate numeric | |||||||||
INTEGER, SMALLINT, DECIMAL | FLOAT, SMALLFLT | CHAR, VARCHAR | NCHAR, NVARCHAR | MCHAR, MVARCHAR | ||||||
DF | EK | U16 | ||||||||
Numeric data | Exact numeric | INTEGER, SMALLINT, DECIMAL | Y | Y | Y | Y | Y | N | Y | |
Approximate numeric | FLOAT, SMALLFLT | Y | Y | Y | Y | Y | N | Y | ||
Character string data (any character set) | CHAR, VARCHAR | DF | Y | Y | Y | Y#2 | Y | N | Y | |
EK | Y | Y | Y#2 | Y | N | N | N | |||
U16 | Y | Y | Y | N | Y | N | Y | |||
National character data | NCHAR, NVARCHAR | N | N | N | N | N | Y | N | ||
Mixed character data | MCHAR, MVARCHAR | Y | Y | Y | N | Y | N | Y | ||
Boolean data | BOOLEAN | N | N | Y | Y | Y | N | Y | ||
Date data | DATE | N | N | Y#1 | Y#1 | Y#1 | N | Y#1 | ||
Time data | TIME | N | N | Y#1 | Y#1 | Y#1 | N | Y#1 | ||
Time stamp data | TIMESTAMP | N | N | Y#1 | Y#1 | Y#1 | N | Y#1 | ||
Date interval data | INTERVAL YEAR TO DAY | Y | N | N | N | N | N | N | ||
Time interval data | INTERVAL HOUR TO SECOND | Y | N | N | N | N | N | N | ||
Binary data | BINARY | N | N | Y | Y | Y | N | N |
Table 2-91 Data type convertibility between the result of the value expression and AS data type (2/2)
Data type of result of value expression | AS data type | ||||||||
---|---|---|---|---|---|---|---|---|---|
Date data | Time data | Time stamp data | Date interval data | Time interval data | Binary data | ||||
DATE | TIME | TIMESTAMP | INTERVAL YEAR TO DAY | INTERVAL HOUR TO SECOND | BINARY | ||||
Numeric data | Exact number | INTEGER, SMALLINT, DECIMAL | N | N | N | Y | Y | N | |
Approximate number | FLOAT, SMALLFLT | N | N | N | N | N | N | ||
Character string data | CHAR, VARCHAR | DF | Y | Y | Y | N | N | Y | |
EK | Y | Y | Y | N | N | Y | |||
U16 | Y | Y | Y | N | N | Y | |||
National character data | NCHAR, NVARCHAR | N | N | N | N | N | N | ||
Mixed character data | MCHAR, MVARCHAR | Y | Y | Y | N | N | N | ||
Boolean data | BOOLEAN | N | N | N | N | N | N | ||
Date data | DATE | Y | N | Y | N | N | N | ||
Time data | TIME | N | Y | Y | N | N | N | ||
Time stamp data | TIMESTAMP | Y | Y | Y | N | N | N | ||
Date interval data | INTERVAL YEAR TO DAY | N | N | N | Y | Y | N | ||
Time interval data | INTERVAL HOUR TO SECOND | N | N | N | Y | Y | N | ||
Binary data | BINARY | N | N | N | N | N | Y |
Table 2-92 Rules for the conversion of date interval data and time interval data into numeric data
Data type of result in value expression | Length specified in AS data type |
---|---|
INTERVAL YEAR TO DAY | Precision 8, scaling 0 |
INTERVAL HOUR TO SECOND | Precision 6, scaling 0 |
Table 2-93 Rules for conversion into character data and mixed character data
Relationship between length of result of value expression and length of AS data type | Data type of result of value expression | |
---|---|---|
Character data and mixed character data | Other than character data or mixed character data | |
length-of-value-expression-result < length-specified-in-AS-data-type | If the data type specified for the AS data type has a fixed length, the data is left-justified and filled with trailing spaces (of the character set of the data type of the result). | |
length-of-value-expression-result = length-specified-in-AS-data-type | Normal conversion | |
length-of-value-expression-result > length-specified-in-AS-data-type | The data is left-justified and the remainder is truncated. If the truncated data contains characters other than spaces (of the character set of the data type of the result), 'W' is set to the SQLWARN1 area.# | An error may occur. |
Table 2-94 Conversion rules for date data, time data, and time stamp data
Data type of result of value expression | AS data type | Conversion rule |
---|---|---|
DATE | DATE | Not converted. |
TIMESTAMP(p2) | The time part is converted as '00:00:00'. The fractional second part is zero-filled. | |
TIME | TIME | Not converted. |
TIMESTAMP(p2) | The date part is converted into CURRENT_DATE. The fractional second part is zero-filled. | |
TIMESTAMP(p1) | DATE | Extracts and converts the date part. |
TIME | Extracts and converts the time part. | |
TIMESTAMP(p2) | Not converted. If p1 > p2, the fractional second part is truncated, and if p1 < p2, it is zero-filled. |
Table 2-95 Conversion rules for numeric data into date interval data or time interval data
Format of numeric data | Data type of result of value expression |
---|---|
Precision 8, scaling 0 | INTERVAL YEAR TO DAY |
Precision 6, scaling 0 | INTERVAL HOUR TO SECOND |
Table 2-96 Conversion rules for date interval data and time interval data
Data type of result of value expression | AS data type | Conversion rule |
---|---|---|
INTERVAL YEAR TO DAY | INTERVAL YEAR TO DAY | Not converted. |
INTERVAL HOUR TO SECOND | The date part is converted into a time part, and if the result does not exceed the range of values for INTERVAL HOUR TO SECOND, the data can be converted. If the result exceeds this range, an error may occur. | |
INTERVAL HOUR TO SECOND | INTERVAL YEAR TO DAY | If the time part is greater than 24 hours, it is carried to the date part. Any data less than 24 hours is truncated. |
INTERVAL HOUR TO SECOND | Not converted. |