Oracle 截取时间值函数 EXTRACT

浅浅的花香味﹌ 2021-12-10 02:51 517阅读 0赞

**Syntax**

**extract\_datetime::=**

**![Description of extract\_datetime.gif follows][Description of extract_datetime.gif follows]**

**Purpose**

`EXTRACT` extracts and returns the value of a specified datetime field from a datetime or interval expression. The `expr` can be any expression that evaluates to a datetime or interval data type compatible with the requested field:

*  If `YEAR` or `MONTH` is requested, then `expr` must evaluate to an expression of data type `DATE`, `TIMESTAMP`, `TIMESTAMP` `WITH` `TIME` `ZONE`, `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`, or `INTERVAL` `YEAR` `TO` `MONTH`.
 *  If `DAY` is requested, then `expr` must evaluate to an expression of data type `DATE`, `TIMESTAMP`, `TIMESTAMP` `WITH` `TIME` `ZONE`, `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`, or `INTERVAL` `DAY` `TO` `SECOND`.
 *  If `HOUR`, `MINUTE`, or `SECOND` is requested, then `expr` must evaluate to an expression of data type `TIMESTAMP`, `TIMESTAMP` `WITH` `TIME` `ZONE`, `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`, or `INTERVAL` `DAY` `TO` `SECOND`. `DATE` is not valid here, because Oracle Database treats it as ANSI `DATE` data type, which has no time fields.
 *  If `TIMEZONE_HOUR`, `TIMEZONE_MINUTE`, `TIMEZONE_ABBR`, `TIMEZONE_REGION`, or `TIMEZONE_OFFSET` is requested, then `expr` must evaluate to an expression of data type `TIMESTAMP` `WITH` `TIME` `ZONE` or `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE`.

`EXTRACT` interprets `expr` as an ANSI datetime data type. For example, `EXTRACT` treats `DATE` not as legacy Oracle `DATE` but as ANSI `DATE`, without time elements. Therefore, 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` data type.

When you specify `TIMEZONE_REGION` or `TIMEZONE_ABBR` (abbreviation), the value returned is a `VARCHAR2` string containing the appropriate time zone region name or abbreviation. When you specify any of the other datetime fields, the value returned is an integer value of `NUMBER` data type representing the datetime value 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 region names and their corresponding abbreviations, query the `V$TIMEZONE_NAMES` dynamic performance view.

1. 获取当前日期中的年份

SQL> select extract(year from sysdate) as "year" from dual
      2  /
    
          year
    ----------
          2012

2. 获取当前日期中的月份

SQL> select extract(month from sysdate) as "month" from dual
      2  /
    
         month
    ----------
             8

3. 获取当前日期中的日

SQL> select extract(day from date'2012-12-28') as "day" from dual
      2  /
    
           day
    ----------
            28

转载于:https://www.cnblogs.com/oradragon/archive/2012/08/30/2664674.html

[Description of extract_datetime.gif follows]: /images/20211209/a50287bf52e64de6b8f7feaa625476cf.png