15.00 - INTERVAL-to-Numeric 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)

INTERVAL-to-Numeric Conversion

Purpose

Convert an INTERVAL with only one field to an exact numeric data type.

This numeric value is the value of the single numeric field in the INTERVAL record.

CAST Syntax

where:

 

Syntax element …

Specifies …

interval_expression

an INTERVAL expression to be converted.

numeric_data_type

the target numeric type to which the interval expression is to be converted.

numeric_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.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    interval_expression

    an INTERVAL expression to be converted.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
  • numeric_data_type

    the target numeric type to which the interval expression is to be converted.

    ANSI Compliance

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

    Implicit INTERVAL-to-Numeric Conversion

    Teradata Database performs implicit conversion of an Interval data type to an exact numeric data type in some cases. See“Implicit Conversion of DateTime types” on page 586.

    Example  

    Consider the following table definition:

       CREATE TABLE sales_intervals 
          ( sdate DATE
          , sinterval INTERVAL MONTH
          , stotals DECIMAL(5,0));

    The following query uses CAST to convert INTERVAL MONTH values in the sinterval column to INTEGER.

       SELECT stotals,
          (EXTRACT (MONTH FROM sdate)) + (CAST(sinterval AS INTEGER))
       FROM sales_intervals;

    Related Topics

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