Teradata Date and Time Expressions | Teradata Vantage - Teradata Date and Time Expressions - 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™

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.