How to insert empty string into a date column using PDO?

Question

I am using a PDO prepared statement to insert values. One of the values is a date, and sometimes it can be empty. In MySQL schema, the date column is set to allow NULL values.

Let's assume date_column is a date and it allows NULL. When I do this:

$query = "INSERT INTO tbl(date_column) VALUES(?)";
$stmt = $pdo->prepare($query);
$stmt->execute(['']);

This is giving me this error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '' for column 'date_column' at row 1

In phpMyAdmin, I can execute this query without errors. It sets the date to 0000-00-00

INSERT INTO tbl(date_column) VALUES('')

Isn't that the same query that is executed by PDO behind the scenes in the code example above? What's wrong?

All I want is to be able to insert empty string '' as a date without errors. I don't care if it is set to 0000-00-00

@u_mulder the query above is just an example. the actual query that I'm having problems with inserts many values. — marmita

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-10-09T13:03:09

This is too long for a comment.

This is a fundamental problem:

All I want is to be able to insert empty string '' as a date.

Strings are strings, and dates are dates. You should not want to insert an empty string into the column because that makes no sense. Instead, you should want to insert either a NULL value or the default value.

So, your code should look like:

INSERT INTO tbl(date_column)
    VALUES(NULL)

or:

INSERT INTO tbl(date_column)
    VALUES(DEFAULT)

The fact that MySQL does better type checking on prepared queries with parameters is actually a good thing.

How to insert empty string into a date column using PDO?

2 Answers