15.00 - DATE-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)

DATE-to-Character Conversion

Purpose

Converts a DATE value to a character string.

CAST Syntax

where:

 

Syntax element …

Specifies …

date_expression

a date expression to be cast to a character string.

character_data_type

the character data type to which date_expression is to be converted.

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 …

    date_expression

    a date expression to be cast to a character string.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • character_data_type

    the character data type to which date_expression is to be converted.

    server_character_set

    the server character set to use for the conversion.

    If the CHARACTER SET clause is omitted, the user default character set is used for the conversion.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Usage Notes

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

    Restrictions

    DATE types cannot be implicitly or explicitly converted to character types if the server character set is GRAPHIC.

    DATE to CLOB conversion is not supported.

    Forcing a FORMAT on CAST for Converting DATE to Character

    The default format for DATE to character conversion uses the format in effect for the DATE value.

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

    Example  

    The dateform mode of the session is INTEGERDATE and column F1 in the table INTDAT is a DATE value with the explicit format ' YYYY,MMM,DD '.

       SELECT F1 FROM INTDAT ;

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

       F1
       ----------
       1900,Dec,31

    Assume that you want to convert this to a value of CHAR(12), and an explicit output format of ' MMMBDD,BYYYY '. Use nested CAST phrases and a FORMAT to obtain the desired result: a report in character format.

       SELECT
       CAST( (CAST (F1 AS FORMAT 'MMMBDD,BYYYY')) AS CHAR(12))
       FROM INTDAT;

    The result after the nested CASTs is the following report.

       F1
       ------------
       Dec 31, 1900

    The inner CAST establishes the display format for the DATE value and the outer CAST indicates the data type of the desired result.

    Example  

    Suppose you need to create a script to convert date values to the ANSI DATE format, regardless of the source of the DATE value or the DATEFORM mode of the session.

    You can use nested CASTs and a FORMAT to do this as demonstrated by the example that follows.

       SELECT
       CAST( (CAST (F1 AS FORMAT 'YYYY-MM-DD')) AS CHAR(10))
       FROM INTDAT;

    The result after the nested CASTs is the following report.

       F1
       ----------
       1900-12-31

    Related Topics

    For details on data types and data attributes, see SQL Data Types and Literals.