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 |
|
|
|
TIME |
TIMESTAMP |
|
|
TIMESTAMP |
|
|
|
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'. |