提取日期的年月日小時分鐘或秒元件

可以使用 EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue ) 找到 DATE 資料型別的年,月或日元件

SELECT EXTRACT (YEAR  FROM DATE '2016-07-25') AS YEAR,
       EXTRACT (MONTH FROM DATE '2016-07-25') AS MONTH,
       EXTRACT (DAY   FROM DATE '2016-07-25') AS DAY
FROM DUAL;

輸出:

YEAR MONTH DAY
---- ----- ---
2016     7  25

時間(小時,分鐘或秒)元件可以通過以下任一方式找到:

例如:

SELECT EXTRACT( HOUR   FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
       EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
       EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM   (
  SELECT TO_DATE( '2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL
);

輸出:

HOURS MINUTES SECONDS
----- ------- -------
    9      42       1