15.00 - Scalar Operations on ANSI SQL:2011 DateTime and Interval Values - 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)
Last Update
2018-09-24

Scalar Operations on ANSI SQL:2011 DateTime and Interval Values

Teradata SQL defines a set of permissible scalar operations for ANSI DateTime and Interval values.

Scalar operations include:

 

Operation

Description

DateTime Expressions

Expressions providing a result that is a DateTime value. DateTime expressions have arguments that are also DateTime or Interval expressions.

Interval Expressions

Expressions providing a result that is an Interval. Interval expressions may include components that are Interval, DateTime, or Numeric expressions.

Data Type Compatibility

The Teradata Database convention of performing implicit conversions to resolve expressions of mixed data types is not supported for operations that include ANSI DateTime or Interval values.

To convert ANSI DateTime or Interval expressions, use the CAST function. See “CAST in Explicit Data Type Conversions” on page 590.

The following restrictions apply to the values appearing in all DateTime and Interval scalar operations:

 

IF …

THEN …

two DateTime values appear in the same DateTime expression

both must be DATE types

ELSE both must be TIME types

ELSE both must be TIMESTAMP types.

You cannot mix DATE, TIME, and TIMESTAMP values across type.

a DateTime and Interval values appear in the same DateTime expression

the Interval value must contain only DateTime fields that are also contained within the DateTime value.

two Interval values appear in the same Interval expression

both must be Year-Month intervals

ELSE both must be Day-Time intervals.

You cannot mix Year-Month with Day-Time intervals.