15.00 - UDT-to-TIME 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-TIME Conversion

Purpose

Converts a UDT expression to a TIME 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.

fractional_seconds_precision

a single digit representing the number of significant digits in the fractional portion of the SECOND field.

Values for fractional_seconds_precision range from 0 through 6 inclusive.

The default precision is 6.

time_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 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:

  • FORMAT
  • NAMED
  • TITLE
  • fractional_seconds_precision

    a single digit representing the number of significant digits in the fractional portion of the SECOND field.

    Values for fractional_seconds_precision range from 0 through 6 inclusive.

    The default precision is 6.

    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

    Teradata Database performs implicit UDT-to-TIME 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
  • Performing an implicit data type conversion requires that an appropriate cast definition (see “Usage Notes”) exists that specifies the AS ASSIGNMENT clause.

    If no UDT-to-TIME implicit cast definition exists, Teradata Database looks for a UDT-to-CHAR or UDT-to-VARCHAR cast definition that can substitute for the UDT-to-TIME implicit cast definition. Substitutions are valid because Teradata Database can use the implicit cast definition to cast the UDT to a character data type, and then implicitly cast the character data type to a DATE type. If multiple UDT-to-character implicit cast definitions exist, then Teradata Database returns an SQL error.

    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 TIME WITH TIME ZONE:

       SELECT id, CAST (information AS TIME WITH TIME ZONE) FROM support;

    Related Topics

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