22.

MySQLのSTR_TO_DATEでnullが返る原因と対処｜書式指定子の不一致

編集

MySQLのSTR_TO_DATE関数でNULLが返る最大の原因は、第2引数の書式指定子（フォーマット文字列）が、第1引数の入力文字列の実際の形式と一致していないことです。STR_TO_DATEは入力文字列を指定書式どおりに解釈できなかった場合、エラーではなく静かにNULLを返すため、原因に気づきにくいのが特徴です。本記事では、NULLが返る主因の切り分け方、正しい書式指定子の対応表、Laravelからの安全な実行方法、そして単体SQLでのデバッグ手順までを整理します。

この記事の要点
  • STR_TO_DATEは「文字列＋書式指定子」を日付に変換する関数で、書式が入力と一致しないとNULLを返す。
  • 最頻の原因は書式指定子と入力文字列の不一致。区切り文字（- / /）やゼロ埋めの有無のズレが典型。
  • %iは分、%mは月。この2つの混同は実害が大きい頻出ミス。
  • 前後の空白・全角数字・存在しない日付（例: 2月30日）でもNULLになる。
  • LaravelではwhereRaw / DB::rawで生SQLとして書き、値はバインドで渡す。
  • 原因切り分けはSELECT STR_TO_DATE(...)を単体で実行して確認するのが最短。

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で原因の手がかりが得られます。

編集
Post Share
子ページ

子ページはありません

同階層のページ
  1. SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost'
  2. Add [～] to fillable property to allow mass assignment on [App\～].
  3. PHP Parse error: syntax error, unexpected 'class' (T_CLASS), expecting identifier (T_STRING) or variable (T_VARIABLE) or '{' or '$' in ～
  4. Changing columns for table "～" requires Doctrine DBAL; install "doctrine/dbal"
  5. MethodNotAllowedHttpException No message
  6. Class 'Doctrine\DBAL\Driver\PDOMySql\Driver' not found
  7. production.ERROR: No application encryption key has been specified.
  8. Dotenv values containing spaces must be surrounded by quotes.
  9. Laravel \ Socialite \ Two \ InvalidStateException
  10. The page has expired due to inactivity. Please refresh and try again.
  11. Failed to clone https://github.com/symfony/thanks.git via https, ssh protocol
  12. Illegal offset type
  13. Cannot access protected property Illuminate\Http\Request::$...
  14. Emitted value instead of an instance of Error
  15. 画像保存時にInternal Server Error
  16. Failed to authenticate on SMTP server with username ...
  17. PostTooLargeException
  18. Database hosts array is empty.
  19. Invalid request (Unsupported SSL request)
  20. does not comply with psr-4 autoloading standard. Skipping.
  21. MySQLのSTR_TO_DATE関数を使用するとnullが返却される問題
2026-06-12 15:50:18 atom 80

人気ページ

最近更新/作成されたページ