Exception Handling for converting a MySQL date string to a NULL value

1. If the date format of YYYYmmdd is converted to null, the following record is displayed. One can get the value, and the other cannot get the value ** mysqlgt; SELECTDATE_FORMAT (STR_TO_DATE (lsquo; 2

1. If the date format of YYYYmmdd is converted to null, the following record is displayed. One can get the value, and the other cannot get the value ** mysqlgt; SELECT DATE_FORMAT (STR_TO_DATE (lsquo; 2

1. Convert the YYYYmmdd format to null.

As shown in the following record, one can get the value, and the other cannot get the value **
Mysql> SELECT DATE_FORMAT (STR_TO_DATE ('2014 13:01:01 ',' % Y % m % d % H: % m: % s'), '% H: % m ');
+ ------------------------- +
| DATE_FORMAT (STR_TO_DATE ('2014 13:01:01 ',' % Y % m % d % H: % m: % s'), '% H: % m') |
+ ------------------------- +
| 13:01 |
+ ------------------------- +
1 row in set (0.00 sec)

Mysql> SELECT DATE_FORMAT (STR_TO_DATE ('2014 12:55:00 ',' % Y % m % d % H: % m: % s'), '% H: % m ');
+ ------------------------- +
| DATE_FORMAT (STR_TO_DATE ('2014 12:55:00 ',' % Y % m % d % H: % m: % s'), '% H: % m') |
+ ------------------------- +
| NULL |
+ ------------------------- +
1 row in set, 1 warning (0.00 sec)

Mysql>

2. view the error message:

Mysql> show warnings;
+ --- + -- + ------------------------ +
| Level | Code | Message |
+ --- + -- + ------------------------ +
| Warning | 1411 | Incorrect datetime value: '2014 12:55:00 'for function str_to_date |
+ --- + -- + ------------------------ +
1 row in set (0.00 sec)

Mysql>
The alarm is "Incorrect datetime value: '2014 12:55:00 'for function str_to_date". The Incorrect date format is changed to a standard date format.

3. Change to % Y-% m-% d % H: % I: % s.

Mysql> select DATE_FORMAT (str_to_date ('2017-09-22 13:00:01 ',' % Y-% m-% d % H: % I: % s'), '% H: % I ');
+ -------------------------- +
| DATE_FORMAT (str_to_date ('2017-09-22 13:00:01 ',' % Y-% m-% d % H: % I: % s'), '% H: % I ') |
+ -------------------------- +
| 13:00 |
+ -------------------------- +
1 row in set (0.00 sec)

Mysql> select DATE_FORMAT (str_to_date ('2017-09-22 12:55:00 ',' % Y-% m-% d % H: % I: % s'), '% H: % I ');
+ -------------------------- +
| DATE_FORMAT (str_to_date ('2017-09-22 12:55:00 ',' % Y-% m-% d % H: % I: % s'), '% H: % I ') |
+ -------------------------- +
| 12: 55 |
+ -------------------------- +
1 row in set (0.00 sec)

Mysql>

We can see that in the standard format, the date can be converted from the string to the date format, and then the value can be obtained during the truncation.

Refer to the official website address: # function_str-to-date

4. Confirm again to narrow down the scope, which is % h. Replace the time with uppercase.

In another case, the format is changed to the preparation format of the 13:00:01 table. The score is still not obtained, as shown below:

Mysql> select DATE_FORMAT (str_to_date ('2017-09-22 13:00:01 ',' % Y-% m-% d % h: % I: % s'), '% h: % I '); + bytes + | DATE_FORMAT (str_to_date ('2017-09-22 13:00:01', '% Y-% m-% d % h: % I: % s'), '% h: % I') | + warning + | NULL | + warning + 1 row in set, 1 warning (0.00 sec) mysql> select DATE_FORMAT (str_to_date ('2017-09-22 12:55:00 ',' % Y-% m-% d % h: % I: % s'), '% h: % I '); + bytes + | DATE_FORMAT (str_to_date ('2017-09-22 12:55:00', '% Y-% m-% d % h: % I: % s'), '% h: % I') | + ------------------------------------------------------------------------------ + | + rows + 1 row in set (0.00 sec) mysql>