タイトル: MySQLのSTR_TO_DATE関数を使用するとnullが返却される問題
SEOタイトル: MySQLのSTR_TO_DATEでnullが返る原因と対処｜書式指定子の不一致
MySQLのSTR_TO_DATE関数でNULLが返る最大の原因は、第2引数の書式指定子（フォーマット文字列）が、第1引数の入力文字列の実際の形式と一致していないことです。STR_TO_DATEは入力文字列を指定書式どおりに解釈できなかった場合、エラーではなく静かにNULLを返すため、原因に気づきにくいのが特徴です。本記事では、NULLが返る主因の切り分け方、正しい書式指定子の対応表、Laravelからの安全な実行方法、そして単体SQLでのデバッグ手順までを整理します。
| この記事の要点 |
|---|
|
STR_TO_DATEとは
STR_TO_DATE(str, format)は、文字列strを書式指定子formatに従って解釈し、対応するDATE / DATETIME / TIME値に変換するMySQLの関数です。DATE_FORMAT関数（日付→文字列）のちょうど逆方向の処理にあたります。文字列で受け取った日付データをDATE型として比較・ソート・範囲検索したい場面で使われます。
-- '2026-06-13' を DATE 値に変換する SELECT STR_TO_DATE('2026-06-13', '%Y-%m-%d'); -- 結果: 2026-06-13 |
第1引数の文字列の形と、第2引数の書式指定子の形が完全に対応している限り、正しい日付値が返ります。逆に言えば、両者がわずかでもズレるとNULLになります。
NULLになる主な原因
NULLが返る原因はほぼ次の4パターンに集約されます。発生頻度の高い順に確認してください。
1. 書式指定子と入力文字列の不一致（最頻）
区切り文字やゼロ埋めの有無が入力と書式でズレていると変換に失敗します。たとえば入力がスラッシュ区切りなのに書式がハイフン区切りだと、解釈できずNULLになります。
-- NG: 入力は '/' 区切りなのに書式が '-' 区切り → NULL SELECT STR_TO_DATE('2026/06/13', '%Y-%m-%d'); -- NULL
-- OK: 区切り文字を入力に合わせる SELECT STR_TO_DATE('2026/06/13', '%Y/%m/%d'); -- 2026-06-13 |
2. 前後の空白や全角文字の混入
入力文字列の先頭・末尾に空白がある、あるいは数字が全角（例: ２０２６）になっていると、半角数字を前提とする書式指定子では解釈できずNULLになります。外部入力やExcelからの取り込みデータで起こりがちです。
-- 前後の空白を除去してから変換する SELECT STR_TO_DATE(TRIM(' 2026-06-13 '), '%Y-%m-%d'); -- 2026-06-13 |
3. 存在しない不正な日付
書式は一致していても、暦上ありえない日付（2月30日、13月など）はNULLになります。データ自体の妥当性を疑う必要があります。
SELECT STR_TO_DATE('2026-02-30', '%Y-%m-%d'); -- NULL（2月に30日は存在しない） |
4. 指定子の取り違え（%i と %m など）
分を表す%iと月を表す%mの混同、あるいは大文字小文字の取り違えにより、書式が入力と噛み合わずNULLになります。詳細は次節の対応表で確認してください。
正しい書式指定子の対応表
STR_TO_DATEで使う代表的な書式指定子は次のとおりです。指定子は大文字小文字を区別します（%Yと%yは別物）。
| 指定子 | 意味 | 範囲・例 |
|---|---|---|
%Y | 年（4桁） | 2026 |
%y | 年（2桁） | 26 |
%m | 月（2桁・ゼロ埋め） | 01〜12 |
%d | 日（2桁・ゼロ埋め） | 01〜31 |
%H | 時（24時間・2桁） | 00〜23 |
%i | 分（2桁・ゼロ埋め） | 00〜59 |
%s | 秒（2桁・ゼロ埋め） | 00〜59 |
日時をまとめて変換する場合の典型例です。月は%m、分は%iである点に注意してください。
-- 日付＋時刻を DATETIME に変換 SELECT STR_TO_DATE('2026-06-13 09:30:00', '%Y-%m-%d %H:%i:%s'); -- 結果: 2026-06-13 09:30:00 |
Laravelから使う場合
EloquentやクエリビルダはSTR_TO_DATEを直接のメソッドとして持たないため、DB::rawやwhereRawで生SQLとして記述します。このとき、外部から渡る値は文字列連結せず必ずバインドパラメータで渡し、SQLインジェクションを避けます。
// whereRaw + バインドで日付文字列を比較 $rows = DB::table('orders') ->whereRaw("STR_TO_DATE(order_date_text, '%Y-%m-%d') = ?", ['2026-06-13']) ->get();
// SELECT 句で変換した値を取得する $rows = DB::table('orders') ->select(DB::raw("STR_TO_DATE(order_date_text, '%Y-%m-%d') AS order_date")) ->get(); |
PHP文字列内ではバックスラッシュやクォートのエスケープに注意します。書式指定子の%自体は通常そのまま記述できますが、書式が意図どおりMySQLへ渡っているかは、まず後述の単体SQLで検証しておくと切り分けが容易です。なお、変換結果がNULLのままだとWHERE条件が一致せず「該当件数が0件になる」「該当行が欠落する」といった形で表面化することがあります。
デバッグ手順
アプリ経由ではなく、MySQLクライアントでSTR_TO_DATEを単体実行して原因を切り分けるのが最短ルートです。
-- 1. 問題の文字列と書式をそのまま単体で試す SELECT STR_TO_DATE('2026-06-13', '%Y-%m-%d');
-- 2. NULL なら警告内容を確認する SHOW WARNINGS;
-- 3. 実データの「生の中身」を疑う（前後空白や桁数） SELECT id, CONCAT('[', order_date_text, ']') AS raw_value, LENGTH(order_date_text) AS len FROM orders WHERE STR_TO_DATE(order_date_text, '%Y-%m-%d') IS NULL; |
手順2のSHOW WARNINGSでは「Incorrect datetime value」「Truncated incorrect」といったメッセージが出て、どこで解釈に失敗したかの手がかりになります。手順3のように値を[ ]で囲んで出力すると、前後の空白や想定外の文字が目視で発見しやすくなります。
落とし穴
| 落とし穴 | 内容と対処 |
|---|---|
%iと%mの混同 | %i は分、%m は月。時刻部分でうっかり%mを書くと分が解釈できずNULLになる。%H:%i:%sが正しい並び。 |
| 指定子の大文字小文字 | 指定子は大文字小文字を区別する。%Y（4桁年）と%y（2桁年）、%M（英語月名）と%m（数値月）はそれぞれ別物。入力に合わせて選ぶ。 |
| エラーにならず黙ってNULL | 変換失敗は例外ではなくNULL＋警告で返る。気づかず後続処理に渡すと「0件ヒット」等の二次障害になる。SHOW WARNINGSで確認する。 |
| 区切り文字・ゼロ埋めの差 | 2026-6-3のようにゼロ埋めされていない値も、%m/%dは可変桁を許容するため概ね通るが、区切り文字（- / / / .）の不一致は確実にNULLになる。書式を入力に合わせる。 |
よくある質問（FAQ）
Q1. 書式は合っているのにNULLになります。なぜですか？
入力文字列に見えない空白や全角数字が混入している、または日付自体が不正（2月30日など）の可能性が高いです。CONCAT('[', 値, ']')とLENGTH()で生の中身と桁数を確認し、必要ならTRIM()で空白を除去してから変換してください。
Q2. STR_TO_DATEとDATE_FORMATの違いは何ですか？
STR_TO_DATEは「文字列→日付値」、DATE_FORMATは「日付値→文字列」と、変換の向きが逆です。文字列をDATE型として比較・ソートしたいときはSTR_TO_DATE、日付値を表示用に整形したいときはDATE_FORMATを使います。
Q3. NULLが返ったときにエラーで止めたいのですが？
STR_TO_DATE自体は失敗してもNULLを返すだけで例外を投げません。アプリ側で変換結果がNULLでないかを明示的に判定するか、SQL側でSTR_TO_DATE(...) IS NULLの行を抽出してデータ不正を検知する運用にします。なお、SELECT実行時には警告が記録されるためSHOW WARNINGSで原因の手がかりが得られます。