How does MySQL interpret numbers and strings without delimiters as dates?

If a string or number, even without any delimiters, in the format YYYYMMDDHHMMSS or YYMMDDHHMMSS makes sense when providing a date, then MySQL interprets this string as a valid date.

Given examples of valid and invalid dates -

mysql> Select Timestamp(20171022040536);
+---------------------------+
| Timestamp(20171022040536) |
+---------------------------+
| 2017-10-22 04:05:36       |
+---------------------------+
1 row in set (0.00 sec)

mysql> Select Timestamp('20171022040536');
+-----------------------------+
| Timestamp('20171022040536') |
+-----------------------------+
| 2017-10-22 04:05:36         |
+-----------------------------+
1 row in set (0.00 sec)

mysql> Select Timestamp('171022040536');
+---------------------------+
| Timestamp('171022040536') |
+---------------------------+
| 2017-10-22 04:05:36       |
+---------------------------+
1 row in set (0.00 sec)

The above query shows examples of valid strings as well as numbers that MySQL can interpret as date values.

mysql> Select Timestamp('20171022048536');
+-----------------------------+
| Timestamp('20171022048536') |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

The above query is an example of an invalid string. MySQL returns NULL because the string has the wrong value (85) within one minute.

mysql> Select Timestamp('20171322040536');
+-----------------------------+
| Timestamp('20171322040536') |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

In the above query, MySQL returns NULL because the value of the string month (13) is wrong.

The above is the detailed content of How does MySQL interpret numbers and strings without delimiters as dates?. For more information, please follow other related articles on the PHP Chinese website!