15.00 - TIMESTAMP-to-Character Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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.