Oracle TO_DATE/TO_CHAR 日期格式參數完整速查表與實戰範例

日期格式元素完整對照表

常用格式模板速查

核心操作實戰範例

1. 日期時間間隔運算 (INTERVAL 語法)

-- 當前時間減 7 分鐘/小時/天/月/年
SELECT SYSDATE, SYSDATE - INTERVAL '7' MINUTE FROM DUAL;
SELECT SYSDATE - INTERVAL '7' HOUR FROM DUAL;
SELECT SYSDATE - INTERVAL '7' DAY FROM DUAL;
SELECT SYSDATE - INTERVAL '7' MONTH FROM DUAL;
SELECT SYSDATE - INTERVAL '7' YEAR FROM DUAL;

-- 間隔乘法
SELECT SYSDATE - 8 * INTERVAL '2' HOUR FROM DUAL;  -- 減 16 小時

2. 日期轉字串 (TO_CHAR)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY"年"FMMM"月"FMDD"日" HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-DDD HH:MI:SS') FROM DUAL;  -- 年積日
SELECT TO_CHAR(SYSDATE, 'YYYY-MM IW-D HH:MI:SS') FROM DUAL;  -- ISO 週

3. 字串轉日期 (TO_DATE)

SELECT TO_DATE('2024-01-15 14:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY') FROM DUAL;

4. TRUNC/ROUND 日期截斷與四捨五入

SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;      -- 年初
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;     -- 月初
SELECT TRUNC(SYSDATE, 'DAY') FROM DUAL;       -- 週初 (週日)
SELECT TRUNC(SYSDATE) FROM DUAL;              -- 當天 00:00:00
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL;     -- 月中點四捨五入

5. 毫秒/微秒精度 (TIMESTAMP)

-- 當前高精度時間戳
SELECT CURRENT_TIMESTAMP FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;

-- 格式化輸出毫秒 (FFn: n=1..9 位)
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'MI:SS.FF6') FROM DUAL;

6. 程式執行時間測量 (毫秒級)

DECLARE
  TYPE RC IS REF CURSOR;
  L_RC RC;
  L_DUMMY ALL_OBJECTS.OBJECT_NAME%TYPE;
  L_START NUMBER;
  L_END NUMBER;
BEGIN
  L_START := DBMS_UTILITY.GET_TIME;  -- 百分之一秒
  OPEN L_RC FOR SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE ROWNUM <= 10000;
  LOOP FETCH L_RC INTO L_DUMMY; EXIT WHEN L_RC%NOTFOUND; END LOOP;
  CLOSE L_RC;
  L_END := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('耗時: ' || (L_END - L_START) * 10 || ' ms');
END;
/

NLS 參數影響

日期語言由 NLS_DATE_LANGUAGE 控制：

ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';  -- MON=JAN, DAY=MONDAY
ALTER SESSION SET NLS_DATE_LANGUAGE = 'SIMPLIFIED CHINESE';  -- MON=1月, DAY=星期一

避坑指南

• 格式不匹配：TO_DATE('2024-13-01', 'YYYY-MM-DD') → ORA-01843
• 隱式轉換風險：WHERE 子句 DATE_COL = '2024-01-15' 依賴 NLS_DATE_FORMAT，必須顯式 TO_DATE
• FM抑制前導零/空白FMMonth→'January' (無填充)
• 時區陷阱：SYSDATE 回傳伺服器時區，CURRENT_DATE 回傳 Session 時區，分布式環境必須統一