ANSI Interval Expression Syntax - Advanced SQL Engine - Teradata Database

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
dzx1591742169550.ditamap
dita:ditavalPath
dzx1591742169550.ditaval
dita:id
B035-1211
lifecycle
previous
Product Category
Teradata Vantageā„¢
{ 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
}
start
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
  [ ( precision [, fractional_seconds_precision ] ) ]
end
{ MONTH | HOUR | MINUTE | SECOND } ( fractional_seconds_precision )

Syntax Elements

interval_term
One of the following:
  • 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_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.
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
Defines the beginning of a date or time interval.
If you specify MONTH or SECOND, you must omit TO end.
end
[Optional] Defines the end of a date or time interval.
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.
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 Teradata Vantageā„¢ - Data Types and Literals, B035-1143.

  • Scalar function reference
  • Aggregate function reference
  • (table_expression)
  • Scalar subquery: (interval_expression)
numeric_factor
A signed numeric_primary.
numeric_term
A numeric_factor or a numeric_term multiplied or divided by a numeric_factor.
interval_factor
A signed interval_primary.
precision
[Optional] Specifies the permitted range of digits, in range [0, 4].
Default: 2
fractional_seconds_precision
[Optional] Specifies the fractional precision for values of SECOND, in range [0, 6].
Default: 6
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)
  • Scalar subquery: (numeric_expression)