15.00 - TIME-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

TIME-to-Character Conversion

Purpose

Convert TIME data to a character string.

CAST Syntax

where:

 

Syntax element …

Specifies …

time_expression

the TIME expression to be cast to a character type.

character_data_type

the character type to which the TIME 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 the FORMAT phrase to enable alternative output formatting for the character representations of DateTime data.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    time_expression

    the TIME 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 TIME 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 TIME to CHAR(n) or VARCHAR(n), then n must be equal to or greater than the length of the TIME 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 TIME 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 TIME 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

    TIME to CLOB conversion is not supported.

    You cannot convert a TIME value to a character string when the server character set is GRAPHIC.

    Forcing a FORMAT on CAST for Converting TIME to Character

    The default format for TIME to character conversion is the format in effect for the TIME value.

    You can convert a TIME value to a character string using a FORMAT phrase. The resulting format, however, is the same as the TIME 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 TIME to CHAR and force an explicit FORMAT on the result regardless of the format associated with the TIME value. This is because of the rules for matching FORMAT phrases to data types.

    Example  

    Field T1 in the table INTTIME is a TIME(6) value with the explicit format ' HH:MI:SSDS(6) '. Assume that you want to convert this to a value of CHAR(6), and an explicit output format of ' HHhMIm '.

       SELECT T1 FROM INTTIME ;

    The result (without a type change) is the following report:

                    T1
       ---------------
       05:57:11.362271

    Now use nested CAST phrases and a FORMAT to obtain the desired result: a report in character format.

       SELECT
       CAST( (CAST (T1 AS FORMAT 'HHhMim'))
       AS CHAR(6))
       FROM INTTIME;

    The result after the nested CASTs is the following report.

       T1
       ------
       05h57m

    The inner CAST establishes the display format for the TIME 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.