ORACLE 使用to_char函数和extract函数提取date类型时间的年、月、日、季度等

可以使用to_char()函数和extract()函数进行数据提取

下面是函数to_char()将时间类型转化为字符串类型时使用的参数列表

SELECT SYSDATE FROM DUAL
--PRINT: 2023-06-07 10:23:37
SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL
--PRINT: TWENTY TWENTY-THREE
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL
--PRINT: 2023
SELECT TO_CHAR(SYSDATE,'YYY') FROM DUAL
--PRINT: 023
SELECT TO_CHAR(SYSDATE,'YY') FROM DUAL
--PRINT: 23
SELECT TO_CHAR(SYSDATE,'Y') FROM DUAL
--PRINT: 3
SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL
--PRINT: 2(季度)
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL
--PRINT: 06
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL
--PRINT: 6月 
SELECT TO_CHAR(SYSDATE,'RM') FROM DUAL
--PRINT: VI  
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL
--PRINT: 星期三
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL
--PRINT: 07
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL
--PRINT: 158(一年内的第几天)
SELECT TO_CHAR(SYSDATE,'HH') FROM DUAL
--PRINT: 10
SELECT TO_CHAR(SYSDATE,'HH12') FROM DUAL
--PRINT: 10
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL
--PRINT: 10
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL
--PRINT: 37
SELECT TO_CHAR(SYSDATE,'YYYYQ') FROM DUAL
--PRINT: 20232 (参数可以拼接)

下面是函数extract()将时间类型转化为字符串类型时使用的参数列表

SELECT SYSDATE FROM DUAL
--PRINT: 2023-06-07 11:32:50
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL
--PRINT: 2023
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL
--PRINT: 6
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL
--PRINT: 7