SQLで日付と文字列の変換方法を把握しよう！【MySQL】

SQLで日付を扱う場合、データベースに格納するデータ型としては、日付型または文字列型が頻繁に利用されます。

実際に取得したデータをプログラムで利用する場合、データベースに格納された型とは異なるフォーマットで利用したいケースも少なくありません。

そこで本記事では、データベースから取得した日付関連のデータを日付型と文字列型双方に変換する方法についてご紹介していきたいと思います。

ポテパンダの一言メモ

本記事ではMySQLを利用した変換方法をご紹介していきます。
各種データベースにより、使用できる関数が異なりますので、ご自身の使用するデータベースの関数に読み替えて実施してください。

SQLで日付型から文字列型への変換方法

SQLで日付型から文字列型への変換方法について、最初にご紹介していきたいと思います。

日付型を文字列型へ変換するには「DATE_FORMAT」関数を利用します。

基本的な使い方

DATE_FORMAT関数の基本的な使い方は、下記のように記述します。

SELECT DATE_FORMAT(日付, フォーマット);

日付を指定したフォーマット形式に変換した文字列として抽出します。

フォーマットに使用する書式指定子

フォーマットに使用可能な書式指定子はMySQL公式ドキュメントでご確認頂くことが可能です。

いくつか代表的な書式指定子を本記事でも合わせて掲載しておきます。

サンプル

では実際に現在日時を指定した文字列フォーマットに変換するサンプルで確認してみましょう。

まずは「yyyy-mm-dd hh:ii:ss」形式で表示するサンプルSQLを作成してみます。

SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');

実行した結果が下記の通りです。

+-----------------------------------------+
| DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2020-07-30 16:16:40                     |
+-----------------------------------------+

指定するフォーマットには日本語の文字列を組み込むことも可能です。

下記のSQLは「yyyy年mm月dd日 hh時ii分ss秒」形式で表示するサンプルです。

SELECT DATE_FORMAT(now(), '%Y年%m月%d日 %H時%i分%s秒');

実行した結果が下記の通りです。

+-------------------------------------------------------+
| DATE_FORMAT(now(), '%Y年%m月%d日 %H時%i分%s秒')         |
+-------------------------------------------------------+
| 2020年07月30日 16時20分06秒                             |
+-------------------------------------------------------+

日付型のデータを取得して変換してみよう

次に実際にデータベースに格納されている日付型のデータを取得して、文字列に変換してみましょう。

まず下記のCREATE文でdate型とdatetime型のカラムを持ったテーブルを作成しています。

CREATE TABLE test1(id int, col1 date, col2 datetime);

格納したデータは下記の通りです。

+------+------------+---------------------+
| id   | col1       | col2                |
+------+------------+---------------------+
|    1 | 2020-07-29 | 2020-07-29 14:00:00 |
|    2 | 2020-07-30 | 2020-07-30 14:00:00 |
|    3 | 2020-08-02 | 2020-08-02 05:00:00 |
+------+------------+---------------------+

サンプル

まずcol1のdate型で格納されたデータを抽出して「年月日時分秒」を指定するとどのようになるのか確認してみましょう。

SELECT DATE_FORMAT(col1, '%Y年%m月%d日 %H時%i分%s秒')
FROM test1;

実行した結果が下記の通りです。

+------------------------------------------------------+
| DATE_FORMAT(col1, '%Y年%m月%d日 %H時%i分%s秒')         |
+------------------------------------------------------+
| 2020年07月29日 00時00分00秒                            |
| 2020年07月30日 00時00分00秒                            |
| 2020年08月02日 00時00分00秒                            |
+------------------------------------------------------+

このように日付型を文字列型に変換する際、時分秒まで取得するよう指定すると、「00時00分00秒」の形で表示されます。

続いてcol2のdatetime型で格納されたデータを「年月日」のみで抽出してみましょう。

SELECT DATE_FORMAT(col2, '%Y年%m月%d日')
FROM test1;

実行した結果が下記の通りです。

+--------------------------------------+
| DATE_FORMAT(col2, '%Y年%m月%d日')    |
+--------------------------------------+
| 2020年07月29日                       |
| 2020年07月30日                       |
| 2020年08月02日                       |
+--------------------------------------+

時分秒の値は表示されず「年月日」部分のみにフォーマット変換された形で出力されています。

SQLで文字列型から日付型への変換方法

今度は上記とは反対に文字列型から日付型への変換方法を紹介していきます。

文字列型を日付型へ変換するには「STR_TO_DATE」関数を利用します。

基本的な使い方

STR_TO_DATE関数の基本的な使い方は、下記のように記述します。

SELECT STR_TO_DATE(日付の文字列, フォーマット);

変換後の値が日付と時間を含む場合「datetime」、日付だけの場合「date」、時間だけの場合「time」が型としてそれぞれ返却されます。

ポテパンダの一言メモ

フォーマットに指定する値は上述したDATE_FORMAT関数と同じです。

サンプル

実際に日付を意味する文字列をSTR_TO_DATE関数を使用して日付型に変換してみましょう。

ここで注意して欲しいのが日付型に変換するためには、「文字列」と「フォーマット」の区切り文字を合わせる必要があります。

SELECT STR_TO_DATE('2020/07/30','%Y/%m/%d');

実行した結果が下記の通りです。

+--------------------------------------+
| STR_TO_DATE('2020/07/30','%Y/%m/%d') |
+--------------------------------------+
| 2020-07-30                           |
+--------------------------------------+

下記のように区切り文字が異なるSQLを作成して実行してみましょう。

SELECT STR_TO_DATE('2020/07/30','%Y-%m-%d');

実行した結果が下記の通りです。

+--------------------------------------+
| STR_TO_DATE('2020/07/30','%Y-%m-%d') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

日付型への変換が出来ず「NULL」として返却されています。

文字列型のデータを取得して変換してみよう

ではこちらもデータベースを用意して文字列型のデータを取得して日付型のデータとして抽出してみたいと思います。

まず下記のCREATE文でvarchar型のカラムを持ったテーブルを作成しています。

CREATE TABLE test2(id int, col1 varchar(20));

格納したデータは下記の通りです。

+------+---------------------+
| id   | col1                |
+------+---------------------+
|    1 | 2020/07/28          |
|    2 | 2020-07-29          |
|    3 | あああ               |
|    4 | 2020/07/30 14:00:00 |
+------+---------------------+

サンプル

では作成した文字列型のデータをSTR_TO_DATE関数で変換してみましょう。

SELECT id, STR_TO_DATE(col1, '%Y/%m/%d')
FROM test2;

実行した結果が下記の通りです。

+------+-------------------------------+
| id   | STR_TO_DATE(col1, '%Y/%m/%d') |
+------+-------------------------------+
|    1 | 2020-07-28                    |
|    2 | NULL                          |
|    3 | NULL                          |
|    4 | 2020-07-30                    |
+------+-------------------------------+

ご確認頂けるように日付に当たる文字列とフォーマットとして指定した書式が一致しているデータを日付型として返却しています。

書式違いや日付では無い文字列に関しては「NULL」が返却されます。

ポテパンダの一言メモ

指定したフォーマット部分の書式が一致していれば、指定していない時分秒が文字列に記載されていても問題ありません。

より詳しい使い方を知りたい方はMySQL公式ドキュメントをご参照ください。

さいごに：SQLで日付変換に利用可能な関数はデータベース毎に異なる

本記事では、SQLで日付と文字列を相互に変換する方法についてご紹介してきました。

再度になりますが、今回ご紹介した内容はMySQLで利用可能な関数を使用しています。

各データベース毎に利用出来る関数が異なりますので、ご利用のデータベースから提供されている関数に適宜読み替えて実装してみてください。