17.10 - EXTRACT Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)
EXTRACT (
  { YEAR | MONTH | DAY | HOUR | MINUTE |
    SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE
  } FROM value
)

Syntax Elements

YEAR
The integer value for YEAR to be extracted from the date represented by value.
MONTH
The integer value for MONTH to be extracted from the date represented by value.
DAY
The integer value for DAY to be extracted from the date represented by value.
HOUR
The integer value for HOUR to be extracted from the date represented by value.
MINUTE
The integer value for MINUTE to be extracted from the date represented by value.
TIMEZONE_HOUR
The integer value for TIMEZONE_HOUR to be extracted from the date represented by value.
TIMEZONE_MINUTE
The integer value for TIMEZONE_MINUTE to be extracted from the date represented by value.
SECOND
The integer and decimal values for SECOND are to be extracted from the date represented by value. The returned value has a data type of DECIMAL(8,2).
value
An expression that results in a DateTime, Interval, or UDT value:
value Description
Character string expression that represents a date String must match the 'YYYY-MM-DD' format.
Character string expression that represents a time String must match the 'HH:MI:SS.SSSSSS' format.
Floating point type Value must be a time value encoded with the algorithm HOUR * 10000 + MINUTE * 100 + SECOND.

Only HOUR, MINUTE, and SECOND can be extracted from a floating point value.

Externally created time values can be appropriately encoded and stored in a REAL column to any desired precision if the encoding creates a value representable by REAL without precision loss.

Do not store time values as REAL in any new applications. Instead, use the more rigorously defined ANSI SQL:2011 DateTime data types.

UDT UDT must have an implicit cast that casts between the UDT and any of the following predefined types:
  • Numeric
  • Character
  • DateTime

To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Implicit type conversion of UDTs for system operators and functions, including EXTRACT, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. See Teradata Vantage™ - Database Utilities, B035-1102.

For more information on implicit type conversion of UDTs, see Teradata Vantage™ - Data Types and Literals, B035-1143.

Not a character string expression or floating point type or UDT Expression must evaluate to a DateTime or Interval type.