Teradata SQL provides a data type for DATE values and stores TIME values as encoded numbers with type REAL. This is a Teradata extension of the ANSI SQL:2011 standard and its use is strongly deprecated.
Since both DATE and TIME are encoded values, not simple integers or real numbers, arithmetic operations on these values are restricted.
ANSI DATE and TIME values are stored using appropriate DateTime types and have their own set of rules for DateTime assignment and expressions. For information, see ANSI DateTime and Interval Data Type Assignment Rules and Scalar Operations on ANSI SQL:2011 DateTime and Interval Values.
DATE and Integer Arithmetic
- DATE + INTEGER
- INTEGER + DATE
- DATE - INTEGER
These expressions are not processed as simple addition or subtraction, but rather as explained in the following process:
- The encoded date value is converted to an intermediate value which is the number of days since some system-defined fixed date.
- The integer value is then added or subtracted, forming another value as number of days, since the fixed base date.
- The result is converted back to a date, valid in the Gregorian calendar.