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

Period-to-Character Conversion

Purpose

Converts a Period data type to its canonical character string representation.

Period-to-Character conversion is supported for CHAR and VARCHAR types only. The target type cannot be CLOB.

CAST Syntax

where:

 

Syntax element …

Specifies …

period_expression

the Period data expression to be cast to a character type.

character_data_type

the character type to which the Period data 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 …

    period_expression

    the Period data 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 Period data 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  

    A Period expression can be cast as a character string representation using the CAST function or the Teradata cast syntax, or when forming the output for field mode. Assume L is the maximum length of the formatted character string for the format associated with the Period expression being cast. The resulting character string contains two strings representing the beginning and ending bounds of the Period expression, each up to length L, and each enclosed in apostrophes (' '), separated by comma and a space ( , ), and then enclosed within a left parenthesis and a right parenthesis [( )]. Thus, the maximum length of the resulting character string is 2*L+8. Assume the actual length is K (which may be less than 2*L+8, for example, if the format includes the full names of months and the specific month for a bound is July) and the target type is CHARACTER(n) or VARCHAR(n):

  • If n is equal to K, the period is cast into the resulting character string of length K.
  • If n is greater than K and the target is VARCHAR(n), the period is cast into the resulting character string with length K.
  • If n is greater than K and the target is CHARACTER(n), the period is cast into the resulting character string and trailing pad characters are added to extend to length n.
  • If n less than K and the session is in ANSI mode, a truncation error is reported.
  • If n less than K and the session is in Teradata mode, a truncated string of length n is returned.
  • For data of Period data types with TIME and TIMESTAMP element types, the UTC value of the Period expression is adjusted to the time zone of the value or the current session time zone if the value does not have a time zone. The exception to conversion from UTC is for an ending bound of a PERIOD(TIMESTAMP(n)) value equal to the maximum value that is used to represent UNTIL_CHANGED; in this case, the value is not changed. Due to such adjustments, the ending bound may appear less than the beginning bound in the result, although in UTC the ending bound is greater than the beginning bound. This happens since the hour value for the TIME data type wraps over every 24 hours (that is, the hour value is obtained using 'module 24').

    Example  

    Assume pts is a PERIOD(TIMESTAMP(2)) column in table t with a value of PERIOD '(2005‑02-02 12:12:12.34, 2006-02-03 12:12:12.34)'.

    In the following example, a PERIOD(TIMESTAMP(2)) column is cast as CHARACTER(52) using the CAST function.

       SELECT CAST(pts AS CHARACTER(52)) FROM t;

    The following is returned:

       ('2005-02-02 12:12:12.34', '2006-02-03 12:12:12.34')

    Related Topics

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