Development

Mariabdb Avoid Invalid datetime format: when date is empty

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.

David Carr

David Carr

For the past 12 years, I’ve been developing applications for the web using mostly PHP. I do this for a living and love what I do as every day there is something new and exciting to learn.

In my spare time, the web development community is a big part of my life. Whether managing online programming groups and blogs or attending a conference, I find keeping involved helps me stay up to date. This is also my chance to give back to the community that helped me get started, a place I am proud to be apart of.

Besides programming I love spending time with friends and family and can often be found together going out catching the latest movie, staying in playing games on the sofa or planning a trip to someplace I’ve never been before.