15.00 - Teradata Date and Time Expressions - 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)

Teradata Date and Time Expressions

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” on page 822 and “Scalar Operations on ANSI SQL:2011 DateTime and Interval Values” on page 825.

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.

    DATE and Date Arithmetic

    The DATE - DATE expression is not processed as a simple subtraction, but rather as explained in the following process:

    1 The encoded date values are converted to intermediate values which are each the number of days since a system-defined fixed date.

    2 The second of these values is then subtracted from the first, giving the number of days between the two dates.

    3 The result is returned as if it were in the ANSI SQL:2011 form INTERVAL DAY, though the value itself is an integer.

    Other arithmetic operations on date values may provide results, but those results are not meaningful.

    Example  

    DATE/2 provides an integer result, but the value has no meaning.

    There are no simple arithmetic operations that have meaning for time values. The reason is that a time value is simply a real number with time encoded as:

       (HOUR*10000 + MINUTE*100 + SECOND) 

    where SECOND may include a fractional value.