中科院西安分院oracle培训课件Les16_cn幻灯片.pptVIP

EXTRACT The EXTRACT expression extracts and returns the value of a specified datetime field from a datetime or interval value expression. You can extract any of the components mentioned in the following syntax using the EXTRACT function. The syntax of the EXTRACT function is: SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR] FROM [datetime_value_expression] [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. In the first example on the slide, the EXTRACT function is used to extract the YEAR from SYSDATE. In the second example in the slide, the EXTRACT function is used to extract the MONTH from HIRE_DATE column of the EMPLOYEES table, for those employees who report to the manager whose EMPLOYEE_ID is 100. Instructor Note The Oracle Server lets you derive datetime and interval value expressions. Datetime value expressions yield values of datetime data type. Interval value expressions yield values of interval data type. For more information on these data types refer Oracle9i SQL Reference. TIMESTAMP Conversion Using FROM_TZ The FROM_TZ function converts a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value. The syntax of the FROM_TZ function is as follows: FROM_TZ(TIMESTAMP timestamp_value, time_zone_value) where time_zone_value is a character string in the format TZH:TZM or a character expression that returns a string in TZR (time zone region) with optional TZD format (TZD is an abbreviated time zone