15.00 - Character-to-INTERVAL 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

Character-to-INTERVAL Conversion

Purpose

Converts a character string to an interval value.

CAST Syntax

where:

 

Syntax element …

Specifies …

character_expression

a character expression to be cast to an INTERVAL value.

interval_data_type

an INTERVAL data type to which character_expression is to be converted.

data_attribute

one of the following optional data attributes:

  • NAMED
  • TITLE
  • ANSI Compliance

    This is ANSI SQL:2011 compliant.

    As an extension to ANSI SQL, Teradata supports the specification of data attributes.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    character_expression

    a character expression to be cast to an INTERVAL value.

    data_attribute

    one of the following optional data attributes:

  • NAMED
  • TITLE
  • interval_data_type

    an INTERVAL data type to which character_expression is to be converted.

    ANSI Compliance

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

    Usage Notes

    The character value is trimmed of leading and trailing pad characters and handled as if it was a string literal in the declaration of an INTERVAL string literal.

    Character-to-INTERVAL conversion is supported for CHAR and VARCHAR types only. The source character type cannot be CLOB.

    If the contents of the character string can be converted to a valid INTERVAL, then they are; otherwise, an error is returned.

    You cannot convert a character data type of GRAPHIC to an INTERVAL string literal.

    Example  

    The following query returns ' -265-11'.

       SELECT CAST('-265-11' AS INTERVAL YEAR(4) TO MONTH);

    Example  

    If the source character string contains values not normalized in the INTERVAL form, but which nevertheless can be converted to a proper INTERVAL, the conversion is made.

    For example, the following query returns '-267-06'

       SELECT CAST('265-30' AS INTERVAL YEAR(4) TO MONTH);

    Related Topics

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