15.00 - Arithmetic Operators - 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)

Arithmetic Operators

Purpose  

Adds or subtracts an Interval value to or from a Period value, or adds a Period value to an Interval value.

Syntax  

where:

 

Syntax element...

Specifies...

period_expression

any expression that evaluates to a Period data type.

interval_expression

an expression that evaluates to an INTERVAL data type.

For information on INTERVAL data types, see SQL Data Types and Literals.

Usage Notes  

Assuming that p is a Period expression of element type DATE or TIMESTAMP and v is an Interval expression:

  • p + v and v + p are both equivalent to:
  • PERIOD(BEGIN(p) + v, CASE WHEN END(p) IS UNTIL_CHANGED THEN END(p) ELSE (END(p) + v) END)
  • p - v is equivalent to:
  • PERIOD(BEGIN (p) - v, CASE WHEN END(p) IS UNTIL_CHANGED THEN END(p) ELSE (END(p) - v) END)

    Assuming that p is a Period expression of element type TIME and v is an interval expression:

  • p + v and v + p are both equivalent to:
  • PERIOD(BEGIN(p) + v, END(p) + v)
  • p - v is equivalent to:
  • PERIOD(BEGIN (p) - v, END(p) - v)

    Usage Rules

    The following rules apply to arithmetic operators and Period data types:

  • The interval expression must be a valid interval expression and must follow the rules of an Interval expression (see “ANSI Interval Expressions” on page 834). Otherwise, an error is reported. For example, the interval expression (DATE '2006-02-03' - DATE '2005-02-03') DAY, results in a value of 365 days which cannot fit into the default precision 2 of the interval qualifier DAY; therefore, an error is reported.
  • The period arithmetic operations of adding or subtracting an Interval to or from a period or adding a period to an Interval follow the rules of DateTime expressions. Otherwise, errors are reported. See “ANSI Interval Expressions” on page 834 for details on DateTime expression rules.
  • An interval expression can be subtracted from a Period expression but not vice versa. If a Period expression is subtracted from an interval expression, an error is reported.
  • For a Period expression with an element type of TIME, if the Period arithmetic operation results in a beginning bound less than the ending bound, an error is reported.
  • For a period of element type DATE or TIMESTAMP, if the ending bound is UNTIL_CHANGED, the ending bound in the result ending bound is UNTIL_CHANGED. If the ending bound is not UNTIL_CHANGED and the ending bound in the result evaluates to an UNTIL_CHANGED value, an error is reported.
  • For a period arithmetic operation, one of the operands must be an INTERVAL data type. Otherwise, an error is reported.