ANSI Interval Expression Syntax - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
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)