ANSI Interval Expression Syntax - Analytics Database - 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)