Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
extract_datetime::=
EXTRACT
extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION
or TIMEZONE_ABBR
(abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES
dynamic performance view.
Some combinations of datetime field and datetime or interval value expression result in ambiguity. In these cases, Oracle returns 'UNKNOWN
' (see the examples that follow for additional information).
See Also:
|
The following example returns the year 1998.
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL; EXTRACT(YEARFROMDATE'1998-03-07') --------------------------------- 1998
The following example selects from the sample table hr.employees
all employees who were hired after 1998:
SELECT last_name, employee_id, hire_date FROM employees WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 1998 ORDER BY hire_date; LAST_NAME EMPLOYEE_ID HIRE_DATE ------------------------- ----------- --------- Landry 127 14-JAN-99 Lorentz 107 07-FEB-99 Cabrio 187 07-FEB-99 . . .
The following example results in ambiguity, so Oracle returns 'UNKNOWN
':
SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 -08:00') FROM DUAL; EXTRACT(TIMEZONE_REGIONFROMTIMESTAMP'1999-01-0110:00:00-08:00') ---------------------------------------------------------------- UNKNOWN
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region.