EXTRACT (datetime)
Syntax
extract_datetime::=
Text description of extract_datetime
Purpose
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).
--------------------------------------------------------------------------------
Note:
The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE datatype.--------------------------------------------------------------------------------
See Also:
"Datetime/Interval Arithmetic" for a description of datetime_value_expr and interval_value_expr
Oracle9i Database Reference for information on the dynamic performance views
Examples
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.
Syntax
extract_datetime::=
Text description of extract_datetime
Purpose
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).
--------------------------------------------------------------------------------
Note:
The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE datatype.--------------------------------------------------------------------------------
See Also:
"Datetime/Interval Arithmetic" for a description of datetime_value_expr and interval_value_expr
Oracle9i Database Reference for information on the dynamic performance views
Examples
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.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货