【Oracle】TO_CHAR / TO_DATE 日付フォーマット完全ガイド｜書式モデル全一覧・FM修飾子・NLS_DATE_FORMAT・ORAエラー対処まで解説

-- 現在日時を各フォーマットで出力
SELECT
    TO_CHAR(SYSDATE, 'YYYY-MM-DD')          AS iso_date,
    TO_CHAR(SYSDATE, 'YYYY/MM/DD')          AS jp_date,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS full_ts,
    TO_CHAR(SYSDATE, 'YYYYMMDD')            AS compact,
    TO_CHAR(SYSDATE, 'DD-MON-YYYY')         AS oracle_default
FROM dual;

-- FM なし: ゼロ埋め・スペース埋めあり
SELECT TO_CHAR(DATE '2026-03-05', 'MM/DD') FROM dual;
-- 結果: 03/05

-- FM あり: 先頭ゼロを除去
SELECT TO_CHAR(DATE '2026-03-05', 'FMMM/FMDD') FROM dual;
-- 結果: 3/5

-- MONTH の末尾スペース除去
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM dual;      -- 'MARCH    '（右にスペース）
SELECT TO_CHAR(SYSDATE, 'FMMONTH') FROM dual;    -- 'MARCH'（スペースなし）

-- 実用例: 'YYYY年M月D日' のように日本語で詰めて表示
SELECT TO_CHAR(DATE '2026-03-05', 'FMYYYY"年"MM"月"DD"日"') FROM dual;
-- 結果: 2026年3月5日（ゼロ埋めなし）

-- DDth: 日に序数を付ける
SELECT TO_CHAR(DATE '2026-03-01', 'DDth') FROM dual;   -- 01st
SELECT TO_CHAR(DATE '2026-03-02', 'DDth') FROM dual;   -- 02nd
SELECT TO_CHAR(DATE '2026-03-03', 'DDth') FROM dual;   -- 03rd
SELECT TO_CHAR(DATE '2026-03-04', 'DDth') FROM dual;   -- 04th

-- FM + TH: ゼロ埋めなし序数
SELECT TO_CHAR(DATE '2026-03-01', 'FMDDth') FROM dual; -- 1st

-- DDSP: 日を英語スペルアウト
SELECT TO_CHAR(DATE '2026-03-28', 'DDSP') FROM dual;   -- TWENTY-EIGHT

-- DDSPTH: スペルアウト + 序数
SELECT TO_CHAR(DATE '2026-03-28', 'DDSPTH') FROM dual; -- TWENTY-EIGHTH

-- YEARSP: 年をスペルアウト
SELECT TO_CHAR(SYSDATE, 'YEARSP') FROM dual;           -- TWENTY TWENTY-SIX

-- 書式モデル内に文字列リテラルを埋め込む（ダブルクォートで囲む）
SELECT TO_CHAR(SYSDATE, '"本日は"YYYY"年"FMMM"月"FMDD"日"です。"') FROM dual;
-- 結果: 本日は2026年3月28日です。

-- 英語月名を含む形式
SELECT TO_CHAR(SYSDATE, 'DD MONTH YYYY') FROM dual;  -- 28 MARCH     2026
SELECT TO_CHAR(SYSDATE, 'FMDDth FMMONTH YYYY') FROM dual; -- 28th March 2026

-- 文字列を DATE 型に変換
SELECT TO_DATE('2026-03-28', 'YYYY-MM-DD')           AS d1 FROM dual;
SELECT TO_DATE('28/03/2026', 'DD/MM/YYYY')           AS d2 FROM dual;
SELECT TO_DATE('2026年3月28日', 'YYYY"年"MM"月"DD"日"') AS d3 FROM dual;

-- 時刻を含む変換
SELECT TO_DATE('2026-03-28 14:35:22', 'YYYY-MM-DD HH24:MI:SS') AS dt FROM dual;

-- WHERE 句での使用（最重要パターン）
SELECT * FROM orders
WHERE order_date >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND order_date <  TO_DATE('2026-04-01', 'YYYY-MM-DD');

-- FX なし: ゼロ埋めなしも許容する
SELECT TO_DATE('2026-3-5', 'YYYY-MM-DD') FROM dual;      -- OK（'2026-03-05'と解釈）

-- FX あり: 書式と文字数が完全一致しないとエラー
SELECT TO_DATE('2026-3-5', 'FXYYYY-MM-DD') FROM dual;
-- → ORA-01861: リテラルが書式文字列と一致しません

-- FX + ゼロ埋めあり: OK
SELECT TO_DATE('2026-03-05', 'FXYYYY-MM-DD') FROM dual;  -- OK

-- FX の用途: 入力バリデーション（アプリ側でゼロ埋めを保証したうえで使う）

-- NG: 書式と入力が不一致
SELECT TO_DATE('2026/03/28', 'YYYY-MM-DD') FROM dual;
-- ORA-01861: リテラルが書式文字列と一致しません

-- OK: 書式を入力に合わせる
SELECT TO_DATE('2026/03/28', 'YYYY/MM/DD') FROM dual;

-- NG: 時刻付きの文字列に日付書式のみ指定
SELECT TO_DATE('2026-03-28 14:35:22', 'YYYY-MM-DD') FROM dual;
-- ORA-01830: 書式文字列の変換前に入力文字列が終了しました

-- OK: 時刻書式を追加
SELECT TO_DATE('2026-03-28 14:35:22', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- ミリ秒（FF3）まで変換
SELECT TO_TIMESTAMP('2026-03-28 14:35:22.123', 'YYYY-MM-DD HH24:MI:SS.FF3')
    AS ts_val FROM dual;

-- マイクロ秒（FF6）まで変換
SELECT TO_TIMESTAMP('2026-03-28 14:35:22.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')
    AS ts_val FROM dual;

-- SYSTIMESTAMP を TO_CHAR で書式化（FF3 でミリ秒表示）
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') AS formatted_ts
FROM dual;

-- TZH:TZM でタイムゾーンオフセットを指定
SELECT TO_TIMESTAMP_TZ(
    '2026-03-28 14:35:22 +09:00',
    'YYYY-MM-DD HH24:MI:SS TZH:TZM'
) AS ts_tz FROM dual;

-- TZR でタイムゾーンリージョン名を指定（例: Asia/Tokyo）
SELECT TO_TIMESTAMP_TZ(
    '2026-03-28 14:35:22 Asia/Tokyo',
    'YYYY-MM-DD HH24:MI:SS TZR'
) AS ts_tz FROM dual;

-- タイムゾーン付き TIMESTAMP を TO_CHAR で出力
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM') AS ts_with_tz
FROM dual;
-- 結果例: 2026-03-28 14:35:22.123 +09:00

-- 現在のセッション NLS 設定を確認
SELECT parameter, value FROM NLS_SESSION_PARAMETERS
WHERE parameter IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT',
                    'NLS_TIMESTAMP_TZ_FORMAT', 'NLS_CALENDAR',
                    'NLS_DATE_LANGUAGE');

-- セッション単位で設定を変更
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 設定後: TO_CHAR なしで日付を SELECT しても意図通りに出力される
SELECT SYSDATE FROM dual;  -- 2026-03-28 14:35:22

-- データベース全体のデフォルト確認（インスタンス設定）
SELECT parameter, value FROM NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT';

-- 【危険】暗黙変換に依存したコード
-- NLS_DATE_FORMAT が 'YYYY-MM-DD' のときは動くが、
-- 'DD-MON-YYYY'（Oracle デフォルト）だと ORA-01861 が発生する
SELECT * FROM orders WHERE order_date = '2026-03-28';  -- NG

-- 【推奨】TO_DATE で書式を明示
SELECT * FROM orders
WHERE order_date = TO_DATE('2026-03-28', 'YYYY-MM-DD');  -- OK（環境依存なし）

-- 【推奨】DATE リテラルを使う（ISO 8601形式のみ対応）
SELECT * FROM orders
WHERE order_date = DATE '2026-03-28';  -- OK（時刻は 00:00:00 になる）

-- order_date が DATE 型の場合
-- NG: 暗黙変換でインデックスが使われない可能性がある
SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2026-03-28';
-- → order_date 列に関数が適用されるため通常インデックスが使えない

-- OK: 右辺を変換してインデックスを活かす
SELECT * FROM orders
WHERE order_date >= DATE '2026-03-28'
  AND order_date <  DATE '2026-03-29';

-- OK: 関数ベースインデックスがある場合は TO_CHAR 側でも使える
-- CREATE INDEX idx_order_date_fmt ON orders (TO_CHAR(order_date, 'YYYY-MM-DD'));
SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2026-03-28';

-- TO_DATE で明示的に変換して INSERT（推奨）
INSERT INTO orders (order_id, order_date, amount)
VALUES (1001, TO_DATE('2026-03-28 14:35:22', 'YYYY-MM-DD HH24:MI:SS'), 5000);

-- DATE リテラルを使う方法（時刻は 00:00:00 固定）
INSERT INTO orders (order_id, order_date, amount)
VALUES (1002, DATE '2026-03-28', 5000);

-- TIMESTAMP リテラル（秒精度まで）
INSERT INTO logs (log_id, log_time, message)
VALUES (1, TIMESTAMP '2026-03-28 14:35:22.123456', 'start');

-- 月ごとの売上集計（月を YYYY-MM 形式で表示）
SELECT TO_CHAR(order_date, 'YYYY-MM')    AS month,
       COUNT(*)                           AS order_count,
       SUM(amount)                        AS total_amount
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY TO_CHAR(order_date, 'YYYY-MM');

-- 四半期ごとの集計
SELECT TO_CHAR(order_date, 'YYYY')       AS year,
       TO_CHAR(order_date, 'Q')           AS quarter,
       SUM(amount)                        AS total_amount
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY'), TO_CHAR(order_date, 'Q')
ORDER BY 1, 2;

-- ISO 週番号での集計
SELECT TO_CHAR(order_date, 'IYYY-IW')    AS iso_week,
       COUNT(*)                           AS order_count
FROM orders
GROUP BY TO_CHAR(order_date, 'IYYY-IW')
ORDER BY 1;

-- 帳票用: 日付を日本語で表示（年月日付き）
SELECT TO_CHAR(SYSDATE, 'FMYYYY"年"MM"月"DD"日"') AS print_date FROM dual;
-- → 2026年3月28日

-- ログ用: ミリ秒付きタイムスタンプ
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') AS log_ts FROM dual;
-- → 2026-03-28 14:35:22.123

-- ファイル名用: スペース・コロンなし形式
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS file_ts FROM dual;
-- → 20260328_143522

-- 会計年度表示（4月始まり: 4月〜翌3月を同一年度として表示）
SELECT TO_CHAR(
           ADD_MONTHS(order_date, -3),
           'YYYY'
       ) AS fiscal_year,
       SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(ADD_MONTHS(order_date, -3), 'YYYY')
ORDER BY 1;