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

UDT-to-INTERVAL Conversion

Purpose

Converts a UDT expression to an INTERVAL data type.

CAST Syntax

where:

 

Syntax element …

Specifies …

UDT_expression

an expression that results in a UDT data type.

For details on expressions that can result in UDT data types, see “SQL UDF” on page 1318.

interval_data_definition

the target predefined interval type followed by optional NAMED or TITLE attribute phrases.

ANSI Compliance

This is ANSI SQL:2011 compliant.

As an extension to ANSI, CAST permits the use of data attribute phrases such as FORMAT.

Teradata Conversion Syntax

where:

 

Syntax element …

Specifies …

UDT_expression

an expression that results in a UDT data type.

For details on expressions that can result in UDT data types, see “SQL UDF” on page 1318.

data_attribute

one of the following optional data attributes:

  • NAMED
  • TITLE
  • interval_data_type

    the target predefined interval type to which UDT_expression is to be converted.

    ANSI Compliance

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

    Usage Notes

    Data type conversions involving UDTs require appropriate cast definitions for the UDTs. To define a cast for a UDT, use the CREATE CAST statement. For more information on CREATE CAST, see SQL Data Definition Language.

    Implicit Type Conversion

    Performing an implicit data type conversion requires a cast definition (see “Usage Notes”) that specifies the following:

  • the AS ASSIGNMENT clause
  • a target data type that is in the same INTERVAL family as the target of the implicit cast:
  •  

    This INTERVAL data type …

    Belongs to this INTERVAL family …

  • INTERVAL YEAR
  • INTERVAL YEAR TO MONTH
  • INTERVAL MONTH
  • Year-Month

  • INTERVAL DAY
  • INTERVAL DAY TO HOUR
  • INTERVAL DAY TO MINUTE
  • INTERVAL DAY TO SECOND
  • INTERVAL HOUR
  • INTERVAL HOUR TO MINUTE
  • INTERVAL HOUR TO SECOND
  • INTERVAL MINUTE
  • INTERVAL MINUTE TO SECOND
  • INTERVAL SECOND
  • Day-Time

    The target data type of the cast definition does not have to be an exact match to the target of the implicit type conversion.

    Teradata Database performs implicit UDT-to-INTERVAL conversions for the following operations:

  • UPDATE
  • INSERT
  • Passing arguments to stored procedures, external stored procedures, UDFs, and UDMs
  • Specific system operators and functions identified in other sections of this book, unless the DisableUDTImplCastForSysFuncOp field of the DBS Control Record is set to TRUE
  • Example  

    Consider the following table definition, where datetime_record is a UDT:

       CREATE TABLE support
          (id INTEGER
          ,information datetime_record );

    Assuming an appropriate cast definition exists for the datetime_record UDT, the following statement converts the values in the information column to INTERVAL MONTH:

       SELECT id, CAST (information AS INTERVAL MONTH) FROM support;

    Related Topics

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