Databases
StackOverflow2013
Postsdbo
POMySQL date field contains no value - having problem importing
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL date field contains no value - having problem importing
    text
    copied!<p>I'm migrating some data into MySQL and the dump from the other database has left a particular date field blank.</p> <p>The table looks like this: </p> <pre><code>+------------------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+------------+-------+ | lngID | int(11) | NO | PRI | 0 | | | dtmDateSubmitted | date | NO | | 0000-00-00 | | </code></pre> <p>This insert looks like this:</p> <pre><code>INSERT INTO tblReports VALUES (1012,'', ... </code></pre> <p>The date field which got dumped for a large number of the records is null but represented as: <code>''</code>.</p> <p>I'm getting the following error when trying to import this:</p> <pre><code>ERROR 1292 (22007) at line 1: Incorrect date value: '' for column 'dtmDateSubmitted' at row 1 </code></pre> <p>Am feeling a bit dumb because really I've never understood the null/blank deal properly and the docs are killing me.</p> <p>Is it that 'date' fields in MySQL doesn't allow <code>''</code> values under any circumstances? It needs to be <code>NULL</code>? (For the record I've tried a stack of combinations between the null and default options to no avail).</p> <p>Normally I'd change <code>''</code> to <code>NULL</code> 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?</p> <p>I suspect I could be fundamentally misunderstanding the way MySQL works, and replacing <code>''</code> with <code>NULL</code> <em>is</em> the proper way to deal with this.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload