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

Character-to-Period Conversion

Purpose

Converts a character string to a Period value.

CAST Syntax

where:

 

Syntax element …

Specifies …

character_expression

a character expression to be cast to a Period value.

period_data_type

Period data type to which character_expression is to be converted.

data_attribute

one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

    This is ANSI SQL:2011 compliant.

    Usage Notes

    A character value expression can be cast as PERIOD(DATE), PERIOD(TIME), or PERIOD(TIMESTAMP) using the CAST function or implicit casting. A character input value can also be implicitly cast as a Period type.

    After any leading and trailing pad characters in the source character value are trimmed, the resulting character string must conform to the format of the target type. Conversion of the beginning and ending portions of the character value expression to corresponding DateTime values follow the existing rules of CHARACTER/VARCHAR to DateTime data type conversions.

    The existing rules include conversion of the source value with a TIME or TIMESTAMP format to UTC based on the specified time zone in the source or, if not specified, the current session time zone. The exception to conversion to UTC for Period data types is when the ending portion of the source character is a TIMESTAMP value without a time zone and the value is equal to the maximum value that is used to represent UNTIL_CHANGED; in this case, the value is not changed to UTC.

    If the target type has a TIME or TIMESTAMP element type and the beginning or ending bound portions of the character value expression contains leap seconds, the seconds portion gets adjusted to 59.999999 with the precision truncated to the target precision.

    If target type has a TIME or TIMESTAMP element type and the target precision is lower than either precision specified in the source character string, an error is reported. If the target precision is higher than a precision specified for a bound in the source character string, trailing zeros are added to the fractional seconds of the corresponding bound of the Period value resulting from the cast.

    The target elements are set to the corresponding resulting values.

    If the result beginning bound is not less than the result ending bound in their UTC forms, an error is reported.

    If an ANSI DateTime format is used to interpret the character data during conversion, then enclosing the beginning and ending values inside apostrophes is optional. For details, see “Character Strings that Use ANSI DateTime Format” on page 620.

    Implicit Character-to-Period Conversion

    A CHARACTER or VARCHAR value is implicitly cast as a Period data type for an assignment, update, insert, merge, or parameter passing operation when the target site has a Period data type and for a comparison operation if the other operand has a Period data type. If any other non-Period value is directly assigned to a Period target site, an error is reported. In the same manner, if any other non-Period value is directly compared to a Period value, an error is reported.

    Note: In some cases, a value may be explicitly cast as a Period data type in order to avoid this error.

    During implicit conversion from CHARACTER or VARCHAR to Period data type, the ANSI DateTime format string is used to interpret the beginning and ending element values in the character string, if the response mode is other than the Field mode or if the character string data is parameterized. If the response mode is Field mode and if the character string data is not parameterized, then the target period format is used to interpret the beginning and ending element values in the character string. The following table describes this in detail.

     

    Mode

    Parameterized Data Present

    Format for Implicit Cast Interpretation

    Field

    No

    Target format

    Field

    Yes

    ANSI format

    Non-field

    Yes

    ANSI format

    Non-field

    No

    ANSI format

    When the ANSI DateTime format string is used to interpret the beginning and ending element values in the character string, enclosing the beginning and ending values inside the apostrophes is optional. This relaxation applies even during an explicit cast. For details, see “Character Strings that Use ANSI DateTime Format” on page 620.

    Character Strings that Use ANSI DateTime Format

    Here is a list of valid character string representations when the implicit or explicit character-to-period conversion uses the ANSI DateTime format to interpret the beginning and ending bound elements.

  • '(''beginning_element_value'',Δ''ending_element_value'')'
  • '(beginning_element_value,Δending_element_value)'
  • '(''beginning_element_value'',Δending_element_value)'
  • '(beginning_element_value,Δ''ending_element_value'')'
  • where formats of beginning_element_value and ending_element_value depend on the target data type.

     

    Target Data Type

    Format

    PERIOD(DATE)

    YYYY-MM-DD

    PERIOD(TIME[(n)])

    HH:MI:SS.S(F)

    PERIOD(TIMESTAMP[(n)])

    YYYY-MM-DDBHH:MI:SS.S(F)

    For the meanings of the format characters, see the description of the FORMAT phrase in SQL Data Types and Literals.

    Example  

    In the following example, two concatenated character literals are cast as PERIOD(TIMESTAMP(2)). The output is adjusted according to the current session time zone during display. Assume the current session time zone displacement is INTERVAL -'08:00' HOUR TO MINUTE and the format derived from SDF is 'YYYY-MM-DDBHH:MI:SS.S(2)Z'.

       SELECT CAST('(''2005-02-02 12:12:12.34+08:00'', ' ||
              '''2006-02-03 12:12:12.34+08:00'')'
              AS PERIOD(TIMESTAMP(2)));

    The following PERIOD(TIMESTAMP(2)) value is returned:

       ('2005-02-01 20:12:12.34', '2006-02-02 20:12:12.34')

    Related Topics

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