Teradata Date and Time Expressions | Teradata Vantage - 17.00 - 17.05 - Teradata Date and Time Expressions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)

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

The following arithmetic functions can be performed with date and an integer (INTEGER is interpreted as a number of days):
  • DATE + INTEGER
  • INTEGER + DATE
  • DATE - INTEGER

These expressions are not processed as simple addition or subtraction, but rather as explained in the following process:

  1. The encoded date value is converted to an intermediate value which is the number of days since some system-defined fixed date.
  2. The integer value is then added or subtracted, forming another value as number of days, since the fixed base date.
  3. The result is converted back to a date, valid in the Gregorian calendar.