oracle extract 函数

时间：2010-11-18 来源：hero--008

Oracle EXTRACT()函数 EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. 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. eg.

SQL> select sysdate from dual;
/
select extract(year from sysdate) year from dual;
/
select extract(month from sysdate) month from dual;
/
select extract(day from sysdate) day from dual;
/
select extract(hour from timestamp '2010-11-18 17:57:32') hour from dual;
/
select extract(minute from timestamp '2010-11-18 17:57:32') minute from dual;
/

--but not

select extract(hour from sysdate) from dual;
/
select extract(minute from sysdate) from dual;
/
SYSDATE
---------

18-NOV-10

SQL>
SYSDATE
---------

18-NOV-10

SQL>
      YEAR
----------

      2010

SQL>
      YEAR
----------

      2010

SQL>
     MONTH
----------

        11

SQL>
     MONTH
----------

        11

SQL>
       DAY
----------

        18

SQL>
       DAY
----------

        18

SQL>
      HOUR
----------

        17

SQL>
      HOUR
----------

        17

SQL>
    MINUTE
----------

        57

SQL>
    MINUTE
----------

        57

SQL> SQL> SQL> select extract(hour from sysdate) from dual
                         *
ERROR at line 1:
ORA-30076: invalid extract field for extract source

SQL> select extract(hour from sysdate) from dual
                         *
ERROR at line 1:
ORA-30076: invalid extract field for extract source

SQL> select extract(minute from sysdate) from dual
                           *
ERROR at line 1:
ORA-30076: invalid extract field for extract source

SQL>