15.00 - ANSI DateTime and Interval Data Type Assignment Rules - 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)

ANSI DateTime and Interval Data Type Assignment Rules

Data Type Compatibility and Conversion

The following rules apply to assignments involving ANSI DateTime or Interval data types:

 

IF the source type is …

AND the target type is …

THEN …

DATE

DATE

the types are compatible and assignments do not require conversion.

For compatibility with existing Teradata assignments, non-ANSI operations such as assigning a DATE to an INTEGER or an INTEGER to a DATE (with validity checking) follow existing Teradata assignment rules.

TIME

TIME

the types are compatible and assignments do not require conversion.

The Teradata system value TIME is encoded as a REAL and is not compatible with ANSI TIME or TIME WITH TIME ZONE.

TIMESTAMP

TIMESTAMP

the types are compatible and assignments do not require conversion.

 

 

Year-Month INTERVAL

Year-Month INTERVAL

Day-Time INTERVAL

Day-Time INTERVAL

Numeric

DATE

Teradata Database performs implicit type conversion before the assignment.

See “Implicit Type Conversions” on page 583 for details.

 

 

 

 

 

DATE

  • Character
  • Numeric
  • TIMESTAMP
  • Character

  • DATE
  • TIME
  • TIMESTAMP
  • TIME

    TIMESTAMP

    TIMESTAMP

  • DATE
  • TIME
  • Interval

    Note: The INTERVAL type must have only one field. For example, INTERVAL YEAR.

    Exact Numeric

    Exact Numeric

    Intervala

    For all other source/target data type combinations in assignments involving ANSI DateTime or Interval data types, the types must be explicitly converted.

    To perform explicit conversions on ANSI DateTime or Interval data types, use the CAST function:

    where:

     

    Syntax element …

    Specifies …

    expression

    an expression with known data type to be cast as a different data type.

    ansi_sql_data_type

    the new data type for expression.

    data_definition_list

    the new data type or data attributes or both for expression.

    For more information, see “CAST in Explicit Data Type Conversions” on page 590.

    Interval Data Type Assignment Rules

    The following rules apply to Year-Month INTERVAL assignments.

     

    WHEN …

    THEN …

    the types match

    assignment is straightforward.

    the source is INTERVAL YEAR and the target is INTERVAL YEAR TO MONTH

    the value for MONTH in the target is set to zero.

    the source is INTERVAL MONTH and the target is INTERVAL YEAR TO MONTH

    the source is extended to include the YEAR field initialized to zero, and the resulting interval is normalized.

    For example, if the source is '15' then the extended source is '0-15', normalized to '1-03'.

    the target is INTERVAL MONTH and the source is either INTERVAL YEAR or INTERVAL YEAR TO MONTH

    the source is converted to INTERVAL MONTH before assignment.

    For example, if the source is '2-11', it is converted to '35'.

    the least significant field of the source is lower than that of the target

    the values of fields in the source with precision lower than the least significant field of the target are truncated.

    For example, if a source of INTERVAL '32' MONTH is assigned to a target column of type INTERVAL YEAR, the value stored is '2'.

    The following rules apply to Day-Time INTERVAL assignments.

     

    WHEN …

    THEN …

    the types match

    assignment is straightforward.

    the target is of lower significance than the least significant field of the source

    values for those fields are set to zero.

    For example, if the source is INTERVAL '49:30' HOUR TO MINUTE and it is assigned to a target column of type INTERVAL HOUR(4) TO SECOND(2), the value stored is '49:30:00.00'.

    the target has fields of higher significance than the most significant field of the source

    the source type is extended to match the target type, setting the new fields to zeros, and normalizing the content as the final step.

    For example, if the source is INTERVAL '49:30' HOUR TO MINUTE and it is assigned to a target column of type INTERVAL DAY TO MINUTE, the value stored is '2 1:30'.

    the least significant field of the source is lower than that of the target

    the values of fields in the source with precision lower than the least significant field of the target are truncated.

    For example, if the source is INTERVAL '10:12:58' HOUR TO SECOND and it is assigned to a target column of type INTERVAL HOUR TO MINUTE, the value stored is '10:12'.