Oracle 日期時間儲存與格式化完整指南

Oracle 日期時間儲存與格式化完整指南

NLS 參數對日期的影響

會話的 NLS_LANGUAGE 影響日期顯示語言（月份縮寫等）。範例：

SELECT * FROM nls_session_parameters WHERE rownum < 4;
-- 修改語言
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

DATE 型別儲存與預設格式

• 資料庫內部儲存格式：DD-MON-YYYY（如 09-MAY-15 實際儲存 4 位年）。
• 預設顯示格式：DD-MON-YY。
• 使用 ANSI 標準日期字面值：DATE 'YYYY-MM-DD'。

CREATE TABLE t(t DATE);
INSERT INTO t VALUES ('09-MAY-15');
INSERT INTO t VALUES (DATE '1994-08-25');
INSERT INTO t VALUES (DATE '1994-08-20');
SELECT * FROM t;
-- 輸出: 09-MAY-15, 25-AUG-94, 20-AUG-94

TO_CHAR：日期轉字串

SELECT TO_CHAR(t, 'DD-MON-YYYY') FROM t;
-- 09-MAY-2015, 25-AUG-1994, 20-AUG-1994

SELECT TO_CHAR(t, 'MON-DD-YYYY') FROM t;
-- MAY-09-2015, AUG-25-1994, AUG-20-1994

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY,HH24:MI:SS') FROM DUAL;
-- 09-MAY-2015,23:58:54

TO_DATE：字串轉日期

-- 錯誤：格式不符預設 DD-MON-YY
SELECT TO_DATE('MAY-6-2015') FROM DUAL; -- ORA-01858

-- 正確：符合預設格式
SELECT TO_DATE('5-MAY-2015') FROM DUAL; -- 05-MAY-15

-- 明確指定格式
SELECT TO_DATE('5-MAY-2015 9:50:45', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

-- 組合使用：轉換後再格式化輸出
SELECT TO_CHAR(TO_DATE('5-MAY-2015 9:50:45', 'DD-MON-YYYY HH24:MI:SS'), 'MON-DD-YYYY HH24:MI:SS') FROM DUAL;
-- MAY-05-2015 09:50:45

會話級日期格式設定

ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-YYYY';
SELECT SYSDATE FROM DUAL; -- MAY-10-2015

RR 與 YY 格式的世紀推算規則

YY：兩位年份視為當前世紀。
RR：依規則推算世紀：

SELECT TO_CHAR(TO_DATE('05-MAY-15', 'DD-MM-RR'), 'DD-MM-YYYY') FROM DUAL; -- 05-05-2015
SELECT TO_CHAR(TO_DATE('05-MAY-65', 'DD-MM-RR'), 'DD-MM-YYYY') FROM DUAL; -- 05-05-1965