ANSI Interval Expressions | SQL Date/Time Functions & Expressions | Vantage - 17.00 - 17.05 - ANSI Interval Expressions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)

Performs a computation on an Interval value (or value expression) and returns a single value of the same type.

Definition

An interval expression is any expression that returns a result that is an INTERVAL value.

{ interval_term |
  interval_expression {+|-} interval_term |
  ( date_time_expression [-] date_time_term ) start [ TO end ]
}
interval_term
{ {+|-} interval_primary |
  interval_term {*|/} numeric_factor |
  numeric_term * interval_factor
}
interval_expression
One of the following:
  • a single interval_term.
  • an interval_term added to or subtracted from an interval_expression.
  • the difference between a date_time_expression and a date_time_term (enclosed by parentheses) preceding a start TO end phrase.
interval_term
One of the following expressions:
  • A single interval_factor
  • An interval_term multiplied or divided by a numeric_factor
  • The product of a numeric_term and an interval_factor
interval_factor
A signed interval_primary.
date_time_expression

An expression that evaluates to a DATE, TIME, or TIMESTAMP value.

The form of the expression is one of the following:
  • a single date_time_term.
  • the sum of an interval_expression and a date_time_term expression.
  • the sum or difference of a date_time_expression and an interval_term.
start

A DateTime value with the following syntax that defines the beginning of a date or time interval:

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
  [ ( precision [, fractional_seconds_precision ] ) ]
where:
  • precision specifies the permitted range of digits, ranging from one to four. The default precision is two.
  • fractional_seconds_precision specifies the fractional precision for values of SECOND, ranging from zero to six. The default is six.

MONTH and SECOND values are only permitted when used without TO end.

TO end

A DateTime value with the following syntax that defines the end of a date or time interval:

{ MONTH | HOUR | MINUTE | SECOND } ( fractional_seconds_precision )

where fractional_seconds_precision specifies the fractional precision for values of SECOND, ranging from zero to six. The default is six.

The value for end must be less significant than the value for start.

If start is a YEAR value, then end must be a MONTH value.

numeric_factor
A signed numeric_primary.
numeric_term
A numeric_factor or a numeric_term multiplied or divided by a numeric_factor.
numeric_primary
One of the following elements, any of which must have the appropriate numeric type:
  • Column reference
  • Numeric literal value
  • Scalar function reference
  • Aggregate function reference
  • (table_expression)
A scalar subquery.
  • (numeric_expression)
interval_primary
One of the following elements, any of which must have the appropriate INTERVAL type:
  • Column reference
  • Interval literal value

For details on Interval literals, see "Overview of Interval Literals" in Teradata Vantage™ - Data Types and Literals, B035-1143.

  • Scalar function reference
  • Aggregate function reference
  • (table_expression)
A scalar subquery.
  • (interval_expression)