MySQLでVARCHAR型を日付に変換する方法｜STR_TO_DATEとDATE_FORMATの使い方

MySQLでVARCHAR型のカラムに保存された日付文字列を日付として扱いたい場合、STR_TO_DATE()関数とDATE_FORMAT()関数を組み合わせて変換することができます。この記事では、サンプルテーブルの作成からデータ挿入、変換クエリの実行まで、手順を追って詳しく解説します。

変換に使う基本構文

VARCHAR型を日付に変換する際の基本的な構文は以下の通りです。

SELECT DATE_FORMAT(STR_TO_DATE(カラム名, '入力フォーマット指定子'), '出力フォーマット指定子') AS 任意の別名 FROM テーブル名;

処理の流れとしては、まずSTR_TO_DATE()で文字列を日付値に変換し、その後DATE_FORMAT()で目的の日付形式に整形します。

主なフォーマット指定子

• %Y：4桁の年（例：2020）
• %m：2桁の月（01〜12）
• %d：2桁の日（01〜31）

サンプルテーブルの作成

構文を理解するために、まずテーブルを作成しましょう。テーブル作成のクエリは次の通りです。

mysql> create table VarcharToDate
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Created_Time varchar(100),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (1.10 sec)

テストデータの挿入

INSERTコマンドを使って、いくつかのレコードを挿入します。ここでは「日/月/年」の形式（例：12/1/2016）で日付が保存されている点に注目してください。

mysql> insert into VarcharToDate(Created_Time) values('12/1/2016');
Query OK, 1 row affected (0.14 sec)

mysql> insert into VarcharToDate(Created_Time) values('14/3/2017');
Query OK, 1 row affected (0.18 sec)

mysql> insert into VarcharToDate(Created_Time) values('15/3/2018');
Query OK, 1 row affected (0.21 sec)

mysql> insert into VarcharToDate(Created_Time) values('19/5/2011');
Query OK, 1 row affected (0.18 sec)

mysql> insert into VarcharToDate(Created_Time) values('19/8/2019');
Query OK, 1 row affected (0.15 sec)

mysql> insert into VarcharToDate(Created_Time) values('21/11/2020');
Query OK, 1 row affected (0.23 sec)

登録データの確認

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

mysql> select *from VarcharToDate;
+----+--------------+
| Id | Created_Time |
+----+--------------+
|  1 | 12/1/2016    |
|  2 | 14/3/2017    |
|  3 | 15/3/2018    |
|  4 | 19/5/2011    |
|  5 | 19/8/2019    |
|  6 | 21/11/2020   |
+----+--------------+
6 rows in set (0.00 sec)

VARCHAR型を日付に変換するクエリ

それでは、実際にVARCHAR型を日付へ変換してみます。まずSTR_TO_DATE()関数で文字列を日付に変換し、続いてDATE_FORMAT()関数で「YYYY-MM-DD」形式に整形します。

mysql> select date_format(str_to_date(Created_Time, '%d/%m/%Y'), '%Y-%m-%d') as Date from VarcharToDate;

ここでのポイントは、元データが「日/月/年」の順で保存されているため、入力側のフォーマット指定子として%d/%m/%Yを指定している点です。元データの並び順と指定子が一致していないと、正しく変換できません。

実行結果

上記クエリの実行結果は以下の通りです。すべての日付が「YYYY-MM-DD」形式に統一されました。

+------------+
| Date       |
+------------+
| 2016-01-12 |
| 2017-03-14 |
| 2018-03-15 |
| 2011-05-19 |
| 2019-08-19 |
| 2020-11-21 |
+------------+
6 rows in set (0.00 sec)

まとめ

MySQLでVARCHAR型の日付文字列を日付に変換するには、STR_TO_DATE()で文字列を解析し、DATE_FORMAT()で希望の出力形式に整えるのが基本です。特に、元データが「年・月・日」のどの順序で保存されているかを確認し、それに対応したフォーマット指定子を選ぶことが、正しく変換するための重要なポイントとなります。