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件のままでした。
取り込みは次の順に進めると、エラーで止まらずに済みます。
- 上の CASE 式を SELECT で実行し、結果が NULL になる行を探す
- NULL になった行の元のデータを確かめて、文字列を直す
- 日付のない行(ここでは空の文字列)は 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のアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 リファレンス: STR_TO_DATE()
- MySQL 8.0 リファレンス: DATE_FORMAT()
- MySQL 8.0 リファレンス: MySQL Server Locale Support(lc_time_names)
- MySQL 8.0 リファレンス: Server SQL Modes
アイキャッチ画像: Photo by Behnam Norouzi on Unsplash
