MySQLのDATE_FORMATで日付を書式化 ― 書式指定子の一覧、%iと%Mの取り違え、曜日を日本語で

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

MySQL で日付を 2026/10/29 や 2026年10月29日 の形で出すには DATE_FORMAT を使います。この記事の出力は MySQL 8.4.11（Ubuntu 26.04 の標準パッケージ）で採りました。

まず見る（結論）

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- 2026-10-29 14:05:09

• 分は %i。%M は月名で、'%H:%M' と書くと 14:October になる
• 戻り値は文字列。'%c/%e' で並べ替えると 10/1 が 9/5 より前に来る
• WHERE DATE_FORMAT(created, '%Y-%m') = '2026-03' は索引で絞れない。範囲で書く

書式指定子

2026-10-29 14:05:09.123456（木曜日）を書式化した結果です。

0埋めの違いは1桁の値で分かります。2026-03-05 09:07:03 を '%m/%d %H:%i' で書式化すると 03/05 09:07、'%c/%e %k:%i' では 3/5 9:07 でした。12時間表記では 0時30分が 12:30 AM、12時30分が 12:30 PM です。

定義されていない指定子はエラーにならず、% を取った文字が出ます。'%Q' は Q になりました。

%M と %i、%h と %H の取り違え

SELECT DATE_FORMAT(@d, '%Y-%m-%d %H:%M') AS wrong_minute,
       DATE_FORMAT(@d, '%Y-%m-%d %h:%i') AS h12;
+-----------------------+------------------+
| wrong_minute          | h12              |
+-----------------------+------------------+
| 2026-10-29 14:October | 2026-10-29 02:05 |
+-----------------------+------------------+

他の言語の書式（%M が分）に慣れていると間違えます。%h は12時間表記なので、%p を付けないと午前か午後か区別できません。

曜日・月名を日本語にする

年月日を日本語で区切るだけなら、書式に漢字を入れます。

SELECT DATE_FORMAT(@d, '%Y年%c月%e日');   -- 2026年10月29日

曜日と月名の言語は lc_time_names で決まります。既定は en_US でした。

SET lc_time_names = 'ja_JP';
SELECT DATE_FORMAT(@d, '%Y年%c月%e日(%a) %W %M %b %p');
-- 2026年10月29日(木) 木曜日 10月 10月 PM

DAYNAME() / MONTHNAME() も 木曜日 / 10月 になります。'ja' だけだと ERROR 1649 (HY000): Unknown locale: 'ja' になるので、'ja_JP' と書きます。

SET lc_time_names はその接続だけに効きます。設定を変えずに曜日を出すなら、DAYOFWEEK()（日曜が 1）の値で ELT() から選びます。

SELECT ELT(DAYOFWEEK(@d), '日', '月', '火', '水', '木', '金', '土');   -- 木

戻り値は文字列

CREATE TABLE ... AS SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS s, DATE(NOW()) AS d で作った表の型は、s が varchar(10)、d が date でした。

SELECT DATE_FORMAT(d, '%c/%e') AS md FROM t ORDER BY md;    -- 10/1, 10/15, 11/20, 9/5
SELECT DATE_FORMAT(d, '%c/%e') AS md FROM t ORDER BY d;     -- 9/5, 10/1, 10/15, 11/20

SELECT MAX(DATE_FORMAT(d, '%c/%e')) AS max_md, DATE_FORMAT(MAX(d), '%c/%e') AS md_of_max FROM t;
+--------+-----------+
| max_md | md_of_max |
+--------+-----------+
| 9/5    | 11/20     |
+--------+-----------+

並べ替えや比較は元の日付の列で行い、DATE_FORMAT は表示にだけ使います。

月ごとに集計する

SELECT DATE_FORMAT(created, '%Y-%m') AS ym, COUNT(*) FROM orders
 WHERE created < '2026-04-01' GROUP BY ym ORDER BY ym;
+---------+----------+
| ym      | COUNT(*) |
+---------+----------+
| 2026-01 |    44639 |
| 2026-02 |    40320 |
| 2026-03 |    44640 |
+---------+----------+

'%Y-%m' は0埋めなので、この形なら文字列の順と日付の順が一致します。

条件に使うと索引で絞れません。created に索引がある30万行の表で比べました。

EXPLAIN SELECT COUNT(*) FROM orders WHERE DATE_FORMAT(created, '%Y-%m') = '2026-03';
-- type=index, possible_keys=NULL, rows=299547
EXPLAIN SELECT COUNT(*) FROM orders WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- type=range, key=idx_created, rows=86096

件数はどちらも 44640 です。DATE_FORMAT 側は索引をすべて読み、1行ずつ書式化して比べています。

NULL・不正な値

結果に NULL が混ざっていたら SHOW WARNINGS を見ます。

文字列を日付に戻す（STR_TO_DATE）

SELECT STR_TO_DATE('2026/10/29 14:05', '%Y/%m/%d %H:%i');   -- 2026-10-29 14:05:00
SELECT STR_TO_DATE('2026年10月29日', '%Y年%m月%d日');          -- 2026-10-29
SELECT STR_TO_DATE('29-Oct-2026', '%d-%b-%Y');               -- 2026-10-29

書式が合わないと NULL を返し、Warning 1411 が出ます。INSERT の中で使うと、既定の sql_mode（STRICT_TRANS_TABLES を含む）ではエラーになり、行は入りません。

INSERT INTO t2 VALUES (STR_TO_DATE('2026/10/29', '%Y-%m-%d'));
ERROR 1411 (HY000): Incorrect datetime value: '2026/10/29' for function str_to_date

文字列が書式より長い場合、STR_TO_DATE('2026-10-29 14:05:09', '%Y-%m-%d') は 2026-10-29 を返し、 Warning 1292 Truncated incorrect date value を出しました。

時刻・タイムゾーン

24時間を超える時間は TIME_FORMAT を使います。TIME_FORMAT('25:30:00', '%H時%i分') は 25時30分 でしたが、DATE_FORMAT(TIME '25:30:00', '%H:%i') は 01:30 になりました。TIME_FORMAT に年月の指定子を渡すと 0000-00 になります。

NOW() はサーバーのタイムゾーン（この環境は @@time_zone が SYSTEM、@@system_time_zone が JST）の時刻です。UTC が要るなら UTC_TIMESTAMP() を書式化します。NOW() には小数秒が無いので %f は 000000 になります。マイクロ秒まで出すなら NOW(6) です。

関連: MySQLのEXPLAINの見方 ／ MySQLのBETWEEN ／ PostgreSQLのto_charで日付を書式化