日期提取函数EXTRACT

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression.

EXTRACT( { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM { expr }
)

 

SQL> SELECT E.ENAME,EXTRACT(YEAR FROM E.HIREDATE) E_YEAR,EXTRACT(MONTH FROM E.HIREDATE) E_MONTH FROM EMP E;
ENAME E_YEAR E_MONTH
---------- ---------- ----------
SMITH   1980 12
ALLEN   1981 2
WARD   1981 2
JONES   1981 4
MARTIN 1981 9
BLAKE   1981 5
CLARK   1981 6
SCOTT  1987 4
KING    1981 11
TURNER 1981 9
ADAMS   1987 5
JAMES   1981 12
FORD    1981 12
MILLER 1982 1