| |
1, YYYYmmdd date format is converted to null
See the following records can take to a value less than the value of taking a **
mysql> SELECT DATE_FORMAT (STR_TO_DATE ( '20150922 13:01:01', '% Y% m% d% H:% m:% s'), '% H:% m');
+ ------------------------- +
| DATE_FORMAT (STR_TO_DATE ( '20150922 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 ( '20150922 12:55:00', '% Y% m% d% H:% m:% s'), '% H:% m');
+ ------------------------- +
| DATE_FORMAT (STR_TO_DATE ( '20150922 12:55:00', '% Y% m% d% H:% m:% s'), '% H:% m') |
+ ------------------------- +
| NULL |
+ ------------------------- +
1 row in set, 1 warning (0.00 sec)
mysql>
2, see the error message:
mysql> show warnings;
+ --- + - + ------------------------ +
| Level | Code | Message |
+ --- + - + ------------------------ +
| Warning | 1411 | Incorrect datetime value: '20150922 12:55:00' for function str_to_date |
+ --- + - + ------------------------ +
1 row in set (0.00 sec)
mysql>
Police say is Incorrect datetime value: '20150922 12:55:00' for function str_to_date, incorrect date format, it is replaced with a fairly standard date format
3, replaced with the canonical% Y-% m-% d% H:% i:% s try
mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% H:% i:% s'), '% H:% i');
+ -------------------------- +
| DATE_FORMAT (str_to_date ( '2015-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 ( '2015-09-22 12:55:00', '% Y-% m-% d% H:% i:% s'), '% H:% i');
+ -------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% H:% i:% s'), '% H:% i') |
+ -------------------------- +
| 12:55 |
+ -------------------------- +
1 row in set (0.00 sec)
mysql>
See in the canonical format, date conversion from string to date format, then the interception division are able to get the value.
Refer to the official website address: http: //dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date
4, it is determined to narrow again,% h is the problem, when you get into uppercase
To see a case, the format will become 2015-09-22 13:00:01 table ready format, you can still take less time value, as follows:
mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ----------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 13:00:01', '% Y-% m-% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ----------------------------- +
| NULL |
+ ------------------------------------------------- ----------------------------- +
1 row in set, 1 warning (0.00 sec)
mysql> select DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ----------------------------- +
| DATE_FORMAT (str_to_date ( '2015-09-22 12:55:00', '% Y-% m-% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ----------------------------- +
| 12:55 |
+ ------------------------------------------------- ----------------------------- +
1 row in set (0.00 sec)
mysql>
Then the problem is where? Only Quguan network again searching for answers to find http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_get-format
See the ultimate standard format: '.% Y-% m-% d% H.% i% s', into the try, to see examples of where the difference yet? The difference is that% H and% h ah, as follows:
mysql> select DATE_FORMAT (str_to_date ( '20150922 13:00:01', '% Y% m% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ------------------------- +
| DATE_FORMAT (str_to_date ( '20150922 13:00:01', '% Y% m% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ------------------------- +
| NULL |
+ ------------------------------------------------- ------------------------- +
1 row in set, 1 warning (0.00 sec)
mysql> select DATE_FORMAT (str_to_date ( '20150922 12:55:00', '% Y% m% d% h:% i:% s'), '% h:% i');
+ ------------------------------------------------- ------------------------- +
| DATE_FORMAT (str_to_date ( '20150922 12:55:00', '% Y% m% d% h:% i:% s'), '% h:% i') |
+ ------------------------------------------------- ------------------------- +
| 12:55 |
+ ------------------------------------------------- ------------------------- +
1 row in set (0.00 sec)
mysql>
Then the standard date format:% Y% m% d% H:% i:% s 'or% Y-% m-% d% H:% i:% s' are as follows:
select DATE_FORMAT (str_to_date ( '20150922 13:00:01', '% Y% m% d% H:% i:% s'), '% H:% i');
select DATE_FORMAT (str_to_date ( '20150922 12:55:00', '% Y% m% d% H:% i:% s'), '% H:% i'); |
|
|
|