MySQL Inserting invalid dates

Question

again, this qn was from the practice qns in the mysql certification guide book ...

QUESTION

Here's the structure of a table typetest with three columns (number, string, and dates). As- sume the server is running in MySQL's “forgiving” SQL mode.

mysql> DESCRIBE typetest;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES    | | NULL | |
+--------+---------------------+------+-----+---------+-------+

You perform the following INSERT operation on table typetest:

INSERT INTO typetest VALUES (1000,'yoodoo','999-12-31');

What data values will actually be stored in the table? Provide a short explanation. 

ANSWER

+--------+--------+------------+
| number | string | dates   |
+--------+--------+------------+
|   255 | yoodo | 0000-00-00 |
+--------+--------+------------+

The inserted number 1000 is too big to fit in the TINYINT UNSIGNED column, so the highest pos- sible value (255) is inserted. 'yoodoo' is too long for a CHAR(5) column and is thus truncated to five  characters.  '999-12-31' is  a  date  that  is  earlier  than  the  earliest  possible  DATE value ('1000-01-01'). This is interpreted as an invalid date, so the “zero” date is stored.

when i tried inserting '999-12-31' into a date i get 0999-12-31. i read some where in the certification guide that mysql may be able to insert dates out of the range 1000-01-01 to 9999-12-31. but in the exam what do i answer?

Answer 1

Exams and real life are different things. It's difficult to prove your opinion, if in book writed another. But i would answer that i tested by my own.

Answer 2

Found this reported as a bug. The official response is that you can do it, but it is not supported, and you are on your own.

Answer 3

You can read the Constraints on Invalid Data section. Unfortunately it doesn't give a straight answer for your question. It says "If the date is totally wrong (outside the server's ability to store it), the special “zero” date value '0000-00-00' is stored in the column instead.", and I would say storing 0999 isn't impossible...

Answer 4

Mysql Date Range: 1000-01-01 to 9999-12-31 (ref)

Here you are trying to insert '999-12-31'. Since this date is not in the above range mysql defaults the value to 0