MySQLでVARCHAR型の日付を別の形式に変換する方法

VARCHAR型のカラムに日付データを保存している場合、後から別の形式に変更したくなることがあります。例えば「12/01/2019」のような文字列を「2019-12-01」の形式に統一したいケースです。このような場合は、UPDATE文とSTR_TO_DATE()関数を組み合わせることで、既存のデータを一括して変換できます。

基本構文

日付形式を変換するための基本的な構文は以下のとおりです。

update テーブル名 set カラム名=str_to_date(カラム名,'%m/%d/%Y');

フォーマット指定子「%m/%d/%Y」は、「月/日/年」の順で格納された文字列を解析することを意味します。実際のデータ形式に合わせて、この部分を適宜変更してください。

サンプルテーブルの作成

まず、動作を確認するためのテーブルを作成します。

mysql> create table DemoTable
(
    DueDate varchar(100)
);
Query OK, 0 rows affected (0.56 sec)

テストデータの挿入

次に、INSERT文を使っていくつかのレコードを挿入します。

mysql> insert into DemoTable values('12/01/2019');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('01/31/2016');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('03/17/2018');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('07/25/2017');
Query OK, 1 row affected (0.14 sec)

現在のデータを確認

SELECT文で全レコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は以下のとおりです。

+------------+
| DueDate    |
+------------+
| 12/01/2019 |
| 01/31/2016 |
| 03/17/2018 |
| 07/25/2017 |
+------------+
4 rows in set (0.00 sec)

現時点では、日付が「月/日/年」形式の文字列として保存されていることがわかります。

STR_TO_DATE()で日付形式を変換する

それでは、VARCHAR型の日付データを別の形式に変換するクエリを実行します。

mysql> update DemoTable set DueDate=str_to_date(DueDate,'%m/%d/%Y');
Query OK, 4 rows affected (0.18 sec)
Rows matched: 4 Changed: 4 Warnings: 0

4件すべてのレコードが正常に更新されました。もう一度テーブルの中身を確認してみます。

mysql> select *from DemoTable;

実行結果は以下のとおりです。

+------------+
| DueDate    |
+------------+
| 2019-12-01 |
| 2016-01-31 |
| 2018-03-17 |
| 2017-07-25 |
+------------+
4 rows in set (0.00 sec)

補足：より確実な運用のためのポイント

STR_TO_DATE()関数は、MySQLの標準的な日付形式（YYYY-MM-DD）を返します。上記のようにVARCHAR型のままでも変換結果は保存されますが、日付として正しく扱いたい場合は、カラムの型自体をDATE型やDATETIME型に変更しておくことをおすすめします。ALTER TABLEを使えば、以下のように型変換できます。

ALTER TABLE DemoTable MODIFY DueDate DATE;

こうすることで、日付の比較や範囲検索、ソートなどが正しく行えるようになり、データの整合性も保たれます。