15.00 - TIMESTAMP-to-Character Conversion - Teradata Database

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: FORMAT NAMED TITLE

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: FORMAT NAMED TITLE
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.