ANSI DateTime Expressions | SQL Date/Time Functions & Expressions | Vantage - 17.10 - ANSI DateTime Expressions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Perform a computation on a DATE, TIME, or TIMESTAMP value (or value expression) and return a single value of the same type.

A DateTime expression is any expression that returns a result that is a DATE, TIME, or TIMESTAMP value.

date_time_expression Syntax
{ date_time_term |
  interval_expression + date_time_term |
  date_time_expression {+|-} interval_term
date_time_primary [
  AT { LOCAL | [ TIME ZONE ] { expression | time_zone_string } }

An expression that evaluates to a DATE, TIME, or TIMESTAMP value.

The form of the expression is one of the following:
  • a single date_time_term.
  • the sum of an interval_expression and a date_time_term expression.
  • the sum or difference of a date_time_expression and an interval_term.
A single date_time_primary or a date_time_primary with a time zone specifier of AT LOCAL, AT [TIME ZONE] expression, or AT [TIME ZONE] time_zone_string.
One of the following:
  • a single interval_term.
  • an interval_term added to or subtracted from an interval_expression.
  • the difference between a date_time_expression and a date_time_term (enclosed by parentheses) preceding a start TO end phrase.
One of the following elements, any of which must have the appropriate DateTime type:
  • Column reference
  • DateTime literal value
  • DateTime function reference

    For example, the result of a CASE expression or CAST function or DateTime built-in function such as CURRENT_DATE or CURRENT_TIME.

  • Scalar function reference
  • Aggregate function reference
  • (table_expression)

    A scalar subquery.

  • (date_time_timestamp_expression)
Use the default time zone displacement based on the current session time zone. The current session time zone may be specified as a time zone string or a time zone displacement expressed as an Interval data type that defines the local time zone offset.
AT [TIME ZONE] expression

Use the time zone displacement defined by expression.

The data type of expression should be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE.

AT [TIME ZONE] time_zone_string
time_zone_string determines the time zone displacement.