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.