StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If your table looks like this:</p> <pre><code>create table foo ( name VARCHAR(50), dob DATETIME DEFAULT '1900-01-01 00:00:00' ); </code></pre> <p>... it should <a href="http://sqlfiddle.com/#!2/a5694/1">work just fine</a>.</p> <p>You should note that <em>default</em> value does not imply <em>mandatory</em>. You can still insert a different value or update the row to your liking:</p> <pre><code>insert into foo (name) values ('Jim'), ('Joe'), ('Jack'); insert into foo (name, dob) values ('Jill', '1999-12-31 23:59:59'); update foo set dob = current_timestamp where name='Jack'; </code></pre> <p>That's what probably happened.</p> <p>As of having a value of <code>0000-00-00 00:00:00</code>, it's known bug/feature that MySQL offers. Depending of your SQL mode, you're allowed to store invalid or incomplete dates.</p> <p><strong>Edit:</strong> To avoid the insertion of <code>0000-00-00</code> dates you need to harden the SQL mode:</p> <pre><code>mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE foo SET dob='invalid' WHERE name='Jim'; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> SET sql_mode = 'STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE foo SET dob='invalid' WHERE name='Joe'; ERROR 1292 (22007): Incorrect datetime value: 'invalid' for column 'dob' at row 2 mysql> mysql> SELECT * FROM foo; +------+---------------------+ | name | dob | +------+---------------------+ | Jim | 0000-00-00 00:00:00 | | Joe | 1900-01-01 00:00:00 | | Jack | 1900-01-01 00:00:00 | +------+---------------------+ 3 rows in set (0.00 sec) </code></pre>

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