CODEWITHSUNDEEP
sqlmysql
Williams
Williams

Reputation: 4318

MySQL date field contains no value - having problem importing

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.

Upvotes: 1

Views: 2535

Answers (3)

Kane
Kane

Reputation: 1430

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

Upvotes: 2

pavium
pavium

Reputation: 15118

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.

Upvotes: 0

Aistina
Aistina

Reputation: 12683

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.

Upvotes: 0

Related Questions