Global setting in MySQL to interpret empty string as NULL for Date Field?

Question

I have some PHP and MySql code that is giving me a error when performing an insert statement that tries to put "" into a nullable Date field. I believe the code worked in another environment and therefore am wondering if the environments could be different and it is possible to put empty string into the MySql Date field and have it interpreted as NULL.

I am able to use the string "0000-00-00" to insert into my environment and it does create a NULL value in the date column, however "" is not valid.

Create a before insert trigger. In the trigger code, inspect if new.date_column_name equals an empty string, if yes set new.date_column_name = null;. That's a quick fix on database level. There's no setting on global level that interprets a 0-length string as null. It would be a disaster if there were. — Mjh
– Mjh, Commented Aug 2, 2017 at 13:11
Thanks for the response, I don't believe there is a trigger on that table in the existing database. I have a recent copy of the database that appears to work and believe the triggers are included, I am at a loss how this statement could be possibly working in another environment. — Trevor Foley
– Trevor Foley, Commented Aug 2, 2017 at 13:17

O. Jones · Accepted Answer · 2017-08-02 13:41:20Z

4

The MySQL developers are gradually tightening up the default SQL modes to make MySQL more picky about the data you give it. Newer version have more restrictive defaults than older versions. That's why stuff that used to work doesn't work any more.

Try issuing this command before you try to insert an empty string into a DATE column. It should make MySQL stop complaining.

set session sql_mode = 'ALLOW_INVALID_DATES';

Read this for lots of detail. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_allow_invalid_dates

answered Aug 2, 2017 at 13:41

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trevor Foley Over a year ago

Thank you so much, that appears to be the difference.

Collectives™ on Stack Overflow

Global setting in MySQL to interpret empty string as NULL for Date Field?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related