MySQL では文字列型のデータを日付型のデータに変換を行うことがよくあります。
今回はその変換を行う STR_TO_DATE 関数の使い方について説明します。
DATE_FORMAT 関数は以下の様な引数をとります。
■[STR_TO_DATE]の引数について
STR_TO_DATE(str_data, format_string)
-- str_data : 変換を行う文字列データ
-- format_string : 変換書式文字列('%Y', '%m', '%d' ... などの文字列)
なお、この関数で戻される値は、書式文字列の指定方法により DATETIME値 DATE値 TIME値 の3種類があります。
書式文字列に日付と時間の両方の部分が含まれる場合は DATETIME 値を返し、文字列に日付と時間の部分の一方のみが含まれる場合は DATE または TIME 値を返します。
STR_TO_DATE 関数を使った簡単な例のSQLを以下に示します。
MariaDB [(none)]> SELECT STR_TO_DATE('2024/01/05', '%Y/%m/%d');
+---------------------------------------+
| STR_TO_DATE('2024/01/05', '%Y/%m/%d') |
+---------------------------------------+
| 2024-01-05 |
+---------------------------------------+
1 row in set (0.10 sec)
このSQLの変換書式文字列は年月日の区切りに「/」（スラッシュ）を使っていますが、文字列の方がスラッシュでは無くハイフンにしたらどうなるでしょうか。
早速、試してみますと以下の様になります。結果的にNULLが返されてしまいますので、注意が必要です。（NULLが返されても問題無ければいいのですが）
MariaDB [(none)]> SELECT STR_TO_DATE('2024-01-05', '%Y/%m/%d');
+---------------------------------------+
| STR_TO_DATE('2024-01-05', '%Y/%m/%d') |
+---------------------------------------+
| NULL |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
■[変換書式文字列]について
変換書式文字列として利用できるものは DATE_FORMAT 関数の時と同様に以下の指定子があります。
| 指定子 | 説明 |
|---|---|
| %a | 簡略曜日名 (Sun..Sat) |
| %b | 簡略月名 (Jan..Dec) |
| %c | 月、数字 (0..12) |
| %D | 英語のサフィクスを持つ日付 (0th, 1st, 2nd, 3rd, …) |
| %d | 日、数字 (00..31) |
| %e | 日、数字 (0..31) |
| %f | マイクロ秒 (000000..999999) |
| %H | 時間 (00..23) |
| %h | 時間 (01..12) |
| %I | 時間 (01..12) |
| %i | 分、数字 (00..59) |
| %j | 年間通算日 (001..366) |
| %k | 時 (0..23) |
| %l | 時 (1..12) |
| %M | 月名 (January..December) |
| %m | 月、数字 (00..12) |
| %p | AM または PM |
| %r | 時間、12 時間単位 (hh:mm:ss に AM または PM が続く) |
| %S | 秒数 (0059) |
| %s | 秒数 (0059) |
| %T | 時間、24 時間単位 (hh:mm:ss) |
| %U | 週 (00..53)、日曜日が週の初日、WEEK() モード 0 |
| %u | 週 (00..53)、月曜日が週の初日、WEEK() モード 1 |
| %V | 週 (01..53)、日曜日が週の初日、WEEK() モード 2、%X とともに使用 |
| %v | 週 (01..53)、月曜日が週の初日、WEEK() モード 3、%x とともに使用 |
| %W | 曜日名 (Sunday..Saturday) |
| %w | 曜日 (0=Sunday..6=Saturday) |
| %X | 年間の週、日曜日が週の初日、数字、4 桁、%V とともに使用 |
| %x | 年間の週、月曜日が週の初日、数字、4 桁、%v とともに使用 |
| %Y | 年、数字、4 桁 |
| %y | 年、数字 (2 桁) |
| %% | リテラル % 文字 |
| %x | x (上記にないすべての 「x」) |
いろんな指定子があるのですが、通常使うのは年月日の指定子と24時制の時刻の指定子ぐらいでしょうか。
そこで以下に例を示します。
MariaDB [(none)]> SELECT
-> STR_TO_DATE('2024/01/05', '%Y/%m/%d') as 'DATE値'
-> ,STR_TO_DATE('24/01/05', '%y/%m/%d') as 'DATE値'
-> ,STR_TO_DATE('2024/01/05 16:25:34', '%Y/%m/%d %H:%i:%s') as 'DATETIME値'
-> ,STR_TO_DATE('16:25:34', '%H:%i:%s') as 'TIME値'
-> ,STR_TO_DATE('2024年01月10日', '%Y年%m月%d日') as 'DATE値'
-> ,STR_TO_DATE('2024年01月10日 18時05分29秒', '%Y年%m月%d日 %H時%i分%s秒') as 'DATETIME値'
-> ;
+------------+------------+---------------------+----------+------------+---------------------+
| DATE値 | DATE値 | DATETIME値 | TIME値 | DATE値 | DATETIME値 |
+------------+------------+---------------------+----------+------------+---------------------+
| 2024-01-05 | 2024-01-05 | 2024-01-05 16:25:34 | 16:25:34 | 2024-01-10 | 2024-01-10 18:05:29 |
+------------+------------+---------------------+----------+------------+---------------------+
1 row in set (0.00 sec)
年の数字が2桁指定の場合は正しく「2024年」となりました。年のデータを2桁で扱うことは2000年問題の時の様なことが有りますので、4桁が普通かと思います。
なお、指定子の間に”年月日”などの全角を入れても正しく変換書式文字列を設定すれば変換できました。
PR
コメント