STR_TO_DATEとDATE_FORMATで文字列と日付を変換

壁にかかった黒とオレンジのカレンダー SQL

MySQL で文字列を日付にするには STR_TO_DATE、日付を好きな形の文字列にするには DATE_FORMAT を使います。どちらも %Y や %m のような書式の記号で形を指定します。CSV から取り込んだ ‘2026/11/02’ のような値を DATE 列へ移すとき、書式を間違えると NULL になるうえ、まったく別の日付が入ることもあります。書式の書き方から、取り込みでつまずく例とその直し方、DATE_FORMAT で並び順が崩れる落とし穴まで、同じデータを使って順に確かめます。

以下の結果は MySQL 8.0 で実際に実行したものです。

このシナリオで考える

取引先から届いた注文の CSV を、いったん文字列のままテーブルに入れた場面を考えます。注文日の列には、入力した人によって形の違う値が混ざっています。スラッシュ区切り、ハイフン区切り、月日年の順、存在しない2月30日、空の文字列の5種類です。

CREATE TABLE import_orders (
  id INT PRIMARY KEY,
  customer VARCHAR(20),
  order_date_text VARCHAR(20)
);

INSERT INTO import_orders VALUES
  (1, '佐藤', '2026/11/02'),
  (2, '鈴木', '2026/11/5'),
  (3, '高橋', '2026/2/30'),
  (4, '田中', '2026-11-08'),
  (5, '伊藤', '11/09/2026'),
  (6, '渡辺', ''),
  (7, '山本', '2026/11/10'),
  (8, '中村', '2026/10/28');

この order_date_text を、最終的には DATE 型の列を持つ orders テーブルへ移します。

STR_TO_DATEの基本と書式の記号

STR_TO_DATE(文字列, 書式) は、書式どおりに文字列を読んで日付を返します。書式の中の %Y や %m が年や月の位置を表し、それ以外の文字(/ や 年 など)は文字列の同じ位置にそのまま出てくる必要があります。

SELECT STR_TO_DATE('2026/11/02', '%Y/%m/%d') AS d1,
       STR_TO_DATE('20261102', '%Y%m%d') AS d2,
       STR_TO_DATE('2026年11月2日', '%Y年%m月%d日') AS d3,
       STR_TO_DATE('2026/11/02 14:05:09', '%Y/%m/%d %H:%i:%s') AS dt;
+------------+------------+------------+---------------------+
| d1         | d2         | d3         | dt                  |
+------------+------------+------------+---------------------+
| 2026-11-02 | 2026-11-02 | 2026-11-02 | 2026-11-02 14:05:09 |
+------------+------------+------------+---------------------+

書式に日付の記号だけがあれば DATE、時刻の記号もあれば DATETIME が返ります。d3 のように「2日」と1桁でも %d で読めます。よく使う記号は次のとおりです。

記号 意味 例
%Y 4桁の年 2026
%y 2桁の年 26
%m 月(2桁) 02
%c 月(0埋めなし) 2
%d 日(2桁) 05
%e 日(0埋めなし) 5
%H 時(00〜23) 14
%i 分 05
%s 秒 09

分は %i です。%M は月の名前(November など)なので、分のつもりで %M と書くと別の値になります。2桁の年の %y は、26 なら 2026 年、70 なら 1970 年として読まれます。

SELECT STR_TO_DATE('26/11/02', '%y/%m/%d') AS y26,
       STR_TO_DATE('70/11/02', '%y/%m/%d') AS y70;
+------------+------------+
| y26        | y70        |
+------------+------------+
| 2026-11-02 | 1970-11-02 |
+------------+------------+

書式に合わない文字列はNULLか別の日付になる

取り込んだ8行に、同じ書式 ‘%Y/%m/%d’ をそのまま当ててみます。

SELECT id, order_date_text,
       STR_TO_DATE(order_date_text, '%Y/%m/%d') AS order_date
FROM import_orders;
SHOW WARNINGS;
+----+-----------------+------------+
| id | order_date_text | order_date |
+----+-----------------+------------+
|  1 | 2026/11/02      | 2026-11-02 |
|  2 | 2026/11/5       | 2026-11-05 |
|  3 | 2026/2/30       | NULL       |
|  4 | 2026-11-08      | NULL       |
|  5 | 11/09/2026      | 2011-09-20 |
|  6 |                 | NULL       |
|  7 | 2026/11/10      | 2026-11-10 |
|  8 | 2026/10/28      | 2026-10-28 |
+----+-----------------+------------+

+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2026/2/30' for function str_to_date  |
| Warning | 1411 | Incorrect datetime value: '2026-11-08' for function str_to_date |
| Warning | 1292 | Truncated incorrect date value: '11/09/2026'                    |
| Warning | 1411 | Incorrect datetime value: '' for function str_to_date           |
+---------+------+-----------------------------------------------------------------+

SELECT では、読めない値はエラーにならず NULL と警告(Warning)になります。2月30日(id 3)はない日付なので NULL です。id 4 はハイフン区切りで、書式のスラッシュと合わないので NULL になります。空の文字列(id 6)も NULL です。

一番困るのは id 5 です。’11/09/2026′ が NULL にならず、2011-09-20 という別の日付になりました。%Y が先頭の 11 を年として読み、%m が 09、%d が 2026 の先頭の 20 を読んで、残りの 26 は捨てられています。警告は出ますが、結果の表だけを見ていると気づけません。

文字列の後ろに余分な文字があっても読めてしまいます。前に余分な文字があると NULL です。

SELECT STR_TO_DATE('2026/11/02 10:00', '%Y/%m/%d') AS extra_tail,
       STR_TO_DATE('x2026/11/02', '%Y/%m/%d') AS extra_head;
+------------+------------+
| extra_tail | extra_head |
+------------+------------+
| 2026-11-02 | NULL       |
+------------+------------+

書式が混ざったデータを変換する

複数の書式を試すなら、COALESCE で順に当てる書き方を思いつきます。ところが、この方法では id 5 を直せません。

SELECT id, order_date_text,
       COALESCE(
         STR_TO_DATE(order_date_text, '%Y/%m/%d'),
         STR_TO_DATE(order_date_text, '%m/%d/%Y')
       ) AS order_date
FROM import_orders
WHERE id IN (4, 5);
+----+-----------------+------------+
| id | order_date_text | order_date |
+----+-----------------+------------+
|  4 | 2026-11-08      | NULL       |
|  5 | 11/09/2026      | 2011-09-20 |
+----+-----------------+------------+

最初の書式で 2011-09-20 という値が返ってしまうので、2つ目の書式は試されません。先に文字列の形を正規表現(REGEXP)で見分け、形ごとに書式を選ぶと、取り違えを防げます。

SELECT id, order_date_text,
       CASE
         WHEN order_date_text REGEXP '^[0-9]{4}[/-][0-9]{1,2}[/-][0-9]{1,2}$'
           THEN STR_TO_DATE(REPLACE(order_date_text, '-', '/'), '%Y/%m/%d')
         WHEN order_date_text REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$'
           THEN STR_TO_DATE(order_date_text, '%m/%d/%Y')
       END AS order_date
FROM import_orders;
+----+-----------------+------------+
| id | order_date_text | order_date |
+----+-----------------+------------+
|  1 | 2026/11/02      | 2026-11-02 |
|  2 | 2026/11/5       | 2026-11-05 |
|  3 | 2026/2/30       | NULL       |
|  4 | 2026-11-08      | 2026-11-08 |
|  5 | 11/09/2026      | 2026-11-09 |
|  6 |                 | NULL       |
|  7 | 2026/11/10      | 2026-11-10 |
|  8 | 2026/10/28      | 2026-10-28 |
+----+-----------------+------------+

ハイフン区切りは REPLACE でスラッシュにそろえてから読んでいます。残った NULL は、ない日付の id 3 と空の id 6 だけになりました。

DATE列へ移すとエラー1411で止まる

NULL の行を WHERE で除けば、そのまま DATE 列へ入れられそうに見えます。実際に試すと、文全体がエラーで止まります。

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer VARCHAR(20),
  order_date DATE NOT NULL
);

INSERT INTO orders (id, customer, order_date)
SELECT id, customer, STR_TO_DATE(order_date_text, '%Y/%m/%d')
FROM import_orders
WHERE STR_TO_DATE(order_date_text, '%Y/%m/%d') IS NOT NULL;
ERROR 1411 (HY000): Incorrect datetime value: '2026/2/30' for function str_to_date

MySQL 8.0 の初期設定では、SQL モードに STRICT_TRANS_TABLES(厳格モード)が入っています。厳格モードの INSERT や UPDATE の中では、SELECT なら警告で済む読めない値がエラーになります。WHERE の中の STR_TO_DATE も同じ扱いです。このエラーで1行も入らず、orders は0件のままでした。

取り込みは次の順に進めると、エラーで止まらずに済みます。

  1. 上の CASE 式を SELECT で実行し、結果が NULL になる行を探す
  2. NULL になった行の元のデータを確かめて、文字列を直す
  3. 日付のない行(ここでは空の文字列)は WHERE で除いて INSERT する

ここでは id 3 は2月28日の入力ミスだったとして直します。

UPDATE import_orders SET order_date_text = '2026/2/28' WHERE id = 3;

INSERT INTO orders (id, customer, order_date)
SELECT id, customer,
       CASE
         WHEN order_date_text REGEXP '^[0-9]{4}[/-][0-9]{1,2}[/-][0-9]{1,2}$'
           THEN STR_TO_DATE(REPLACE(order_date_text, '-', '/'), '%Y/%m/%d')
         WHEN order_date_text REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$'
           THEN STR_TO_DATE(order_date_text, '%m/%d/%Y')
       END
FROM import_orders
WHERE order_date_text <> '';

SELECT * FROM orders;
+----+----------+------------+
| id | customer | order_date |
+----+----------+------------+
|  1 | 佐藤     | 2026-11-02 |
|  2 | 鈴木     | 2026-11-05 |
|  3 | 高橋     | 2026-02-28 |
|  4 | 田中     | 2026-11-08 |
|  5 | 伊藤     | 2026-11-09 |
|  7 | 山本     | 2026-11-10 |
|  8 | 中村     | 2026-10-28 |
+----+----------+------------+

空の文字列の行は CASE のどの条件にも合わないので、WHERE で除かないと NOT NULL の列に NULL を入れようとしてエラーになります。INSERT … SELECT の列の合わせ方は「INSERT … SELECTで別テーブルにデータをコピーする方法」で説明しています。

DATE_FORMATで日付を好きな形の文字列にする

DATE_FORMAT(日付, 書式) は、STR_TO_DATE と同じ記号を使って、日付を文字列に変えます。画面の表示や CSV の書き出しで、形をそろえたいときに使います。

SELECT DATE_FORMAT('2026-11-02 14:05:09', '%Y/%m/%d') AS f1,
       DATE_FORMAT('2026-11-02 14:05:09', '%Y年%c月%e日') AS f2,
       DATE_FORMAT('2026-11-02 14:05:09', '%H:%i') AS f3,
       DATE_FORMAT('2026-11-02 14:05:09', '%Y%m%d') AS f4,
       DATE_FORMAT('2026-11-02 14:05:09', '%W') AS f5;
+------------+------------------+-------+----------+--------+
| f1         | f2               | f3    | f4       | f5     |
+------------+------------------+-------+----------+--------+
| 2026/11/02 | 2026年11月2日    | 14:05 | 20261102 | Monday |
+------------+------------------+-------+----------+--------+

f2 のように %c と %e を使うと、「11月2日」のように0を付けずに出せます。曜日の %W は、初期設定では英語の Monday になります。日本語にするには、接続ごとの設定 lc_time_names を ja_JP に変えます。

SET lc_time_names = 'ja_JP';
SELECT DATE_FORMAT('2026-11-02', '%c月%e日(%a)') AS ja_short,
       DATE_FORMAT('2026-11-02', '%W') AS ja_long;
+--------------------+-----------+
| ja_short           | ja_long   |
+--------------------+-----------+
| 11月2日(月)      | 月曜日    |
+--------------------+-----------+

%a は「月」、%W は「月曜日」になりました。lc_time_names はその接続の中だけで有効なので、アプリから使うときは接続のたびに設定します。日付の足し算や差の計算は「MySQLの日付関数まとめ:DATE_ADD・DATEDIFFの使い方」にまとめています。

よくある落とし穴

DATE_FORMAT の結果は日付ではなく文字列です。そのため、DATE_FORMAT した列で並べると、文字の順に並んでしまいます。

SELECT id, DATE_FORMAT(order_date, '%c/%e') AS md
FROM orders
ORDER BY md;
+----+-------+
| id | md    |
+----+-------+
|  8 | 10/28 |
|  7 | 11/10 |
|  1 | 11/2  |
|  2 | 11/5  |
|  4 | 11/8  |
|  5 | 11/9  |
|  3 | 2/28  |
+----+-------+

11/10 が 11/2 より前に来て、2月28日は最後に回りました。表示は DATE_FORMAT で作り、並べるのは元の DATE 列で行うと、日付の順になります。

SELECT id, DATE_FORMAT(order_date, '%c/%e') AS md
FROM orders
ORDER BY order_date;
+----+-------+
| id | md    |
+----+-------+
|  3 | 2/28  |
|  8 | 10/28 |
|  1 | 11/2  |
|  2 | 11/5  |
|  4 | 11/8  |
|  5 | 11/9  |
|  7 | 11/10 |
+----+-------+
  • WHERE DATE_FORMAT(order_date, ‘%Y-%m’) = ‘2026-11’ のように列を関数に通すと、order_date のインデックスが使われません。order_date >= ‘2026-11-01’ AND order_date < ‘2026-12-01’ のように範囲で書きます
  • STR_TO_DATE の結果が NULL かどうかだけを見ていると、id 5 のような取り違えを見落とします。変換したあとに SHOW WARNINGS を見るか、REGEXP で形を確かめてから変換します
  • 日付を文字列の列に入れたまま使い続けると、並べ替えや範囲の検索のたびに変換が要ります。取り込んだら早めに DATE や DATETIME の列へ移します

まとめ

STR_TO_DATE は文字列を日付に、DATE_FORMAT は日付を文字列に変えます。どちらも %Y・%m・%d などの同じ記号を使います。STR_TO_DATE は書式に合わないと NULL を返しますが、’11/09/2026′ のように別の日付として読めてしまう値もあります。形が混ざったデータは REGEXP で見分けてから変換し、厳格モードで INSERT する前に、NULL になる行を SELECT で探して直しておくと、エラー1411で止まらずに移せます。

もっと学ぶなら【PR】

  • MySQL徹底入門 第5版:MySQL の機能を広く解説した本。日付関数などの細かい動きを調べたいときに

※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。

参考リンク

アイキャッチ画像: Photo by Behnam Norouzi on Unsplash

タイトルとURLをコピーしました