TIMESTAMP-to-Character Conversion
Purpose
Convert TIMESTAMP data to a character string.
CAST Syntax
where:
Syntax element … |
Specifies … |
timestamp_expression |
the TIMESTAMP expression to be cast to a character type. |
character_data_type |
the character type to which the TIMESTAMP expression is to be converted. |
server_character_set |
the server character set to use for the conversion. If no CHARACTER SET clause is specified to indicate which server character set to use, the user default server character set is used. |
character_data_attribute |
one of the following optional data attributes: |
ANSI Compliance
This is ANSI SQL:2011 compliant.
As an extension to ANSI, CAST permits the use of character data attribute phrases.
Teradata Conversion Syntax
where:
Syntax element … |
Specifies … |
timestamp_expression |
the TIMESTAMP expression to be cast to a character type. |
data_attribute |
one of the following optional data attributes: |
character_data_type |
the character type to which the TIMESTAMP expression is to be converted. |
server_character_set |
the server character set to use for the conversion. If no CHARACTER SET clause is specified to indicate which server character set to use, the user default server character set is used. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Usage Notes
When converting TIMESTAMP to CHAR(n) or VARCHAR(n), then n must be equal to or greater than the length of the TIMESTAMP value as represented by a character string literal.
IF the target data type is … |
AND n is … |
THEN … |
CHAR(n) |
greater than the length of the TIMESTAMP value as represented by a character string literal |
trailing pad characters are added to pad the representation. |
too small |
a string truncation error is returned. |
|
VARCHAR(n) |
greater than the length of the TIMESTAMP value as represented by a character string literal |
no blank padding is added to the character representation. |
too small |
a string truncation error is returned. |
TIMESTAMP to CLOB conversion is not supported.
You cannot convert a TIME value to a character string if the server character set is GRAPHIC.
Forcing a FORMAT on CAST for Converting TIMESTAMP to Character
The default format for TIMESTAMP to character conversion is the format in effect for the TIMESTAMP value.
To override the format, you can convert a TIMESTAMP value to a string using a FORMAT phrase. The resulting format, however, is the same as the TIMESTAMP value. If you want a different format for the string value, you need to also use CAST as described here.
You must use nested CAST operations in order to convert values from TIMESTAMP to CHAR and force an explicit FORMAT on the result regardless of the format associated with the TIMESTAMP value. This is because of the rules for matching FORMAT phrases to data types.
Example
Field TS1 in the table INTTIMESTAMP is a TIMESTAMP value with the explicit format ' Y4-MM-DDBHH:MI:SSDS(6) '. Assume that you want to convert this to a value of CHAR(19), and an explicit output format of ' M3BDD,BY4BHHhMIm '.
SELECT TS1 FROM INTTIMESTAMP;
The result (without a type change) is the following report:
TS1
--------------------------
1900-12-31 08:25:37.899231
Now use nested CAST phrases and a FORMAT to obtain the desired result: a report in character format.
SELECT
CAST( (CAST (TS1 AS FORMAT 'M3BDD,BY4BHHhMIm'))
AS CHAR(19))
FROM INTTIMESTAMP;
The result after the nested CASTs is the following report.
TS1
-------------------
Dec 31, 1900 08h25m
The inner CAST establishes the display format for the TIMESTAMP value and the outer CAST indicates the data type of the desired result.
Related Topics
For details on data types and data attributes, see SQL Data Types and Literals.