Mariabdb Avoid Invalid datetime format: when date is empty

David Carr

1 min read - 31st May, 2017

When inserting records any columns with a datatype set to date which are empty will generate this error: 

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime

The reason for this by default MariaDB’s sql mode has NO_ZERO_DATE which won’t allow zero dates to be entered only valid or null is allowed.

You have 2 options here:

  1. Change this setting in the database config file 
  2. ensure empty dates are set to null

I’ve chosen to leave the default in place and always pass null for empty dates, ternary operators make this simple:

$startDate = ($startDate !='') ? date('Y-m-d', strtotime($startDate)) : null;

The above says if the date is not empty then convert the date into the correct format otherwise pass null.

Add a comment

Copyright © 2006 - 2024 DC Blog - All rights reserved.