MySQL date field contains no value - having problem importing

Question

I'm migrating some data into MySQL and the dump from the other database has left a particular date field blank.

The table looks like this:

+------------------+-------------+------+-----+------------+-------+
| Field            | Type        | Null | Key | Default    | Extra |
+------------------+-------------+------+-----+------------+-------+
| lngID            | int(11)     | NO   | PRI | 0          |       |
| dtmDateSubmitted | date        | NO   |     | 0000-00-00 |       |

This insert looks like this:

INSERT INTO tblReports VALUES (1012,'', ...

The date field which got dumped for a large number of the records is null but represented as: ''.

I'm getting the following error when trying to import this:

ERROR 1292 (22007) at line 1: 
Incorrect date value: '' for column 'dtmDateSubmitted' at row 1

Am feeling a bit dumb because really I've never understood the null/blank deal properly and the docs are killing me.

Is it that 'date' fields in MySQL doesn't allow '' values under any circumstances? It needs to be NULL? (For the record I've tried a stack of combinations between the null and default options to no avail).

Normally I'd change '' to NULL to make this error go away but it seems so wrong and hackish. Can anybody tell me what a proper/better way to treat this is?

I suspect I could be fundamentally misunderstanding the way MySQL works, and replacing '' with NULL is the proper way to deal with this.

Answer 1

INSERT INTO tblReports (lngID) VALUES (1012)

Should use the default value for the dtmDateSubmitted column. So just don't include that column in your insert statements.

Answer 2

So, how are you inserting the data? By executing SQL statements or programmatically through something like PHP (which generates the insert statements for you)?

Either way, something has to be done about those '' inserted into the date fields and Aistina's suggestion is a good one.

The problem is that '' is just not a valid date and the error would be inevitable.

Could you do a bulk edit of the SQL statements, if that's your method?

Maybe using regular expressions.

Changing the schema would surely cause problems when extracting data - it wouldn't be the format extracted before the change.

Answer 3

Alter your datetime column to varchar. Import sql. Update tbl set dtmDateSubmitted=NULL where dtmDateSubmitted=''; Alter column back to datetime.