提取日期的年月日小時分鐘或秒元件
可以使用 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
時間(小時,分鐘或秒)元件可以通過以下任一方式找到:
- 使用
CAST( datevalue AS TIMESTAMP )
將DATE
轉換為TIMESTAMP
然後使用EXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue )
; 要麼 - 使用
TO_CHAR( datevalue, format_model )
將值作為字串獲取。
例如:
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