MySQL日期字符串轉換成NULL值的異常處理

MySQL日期字符串轉換成NULL值的異常處理

1，YYYYmmdd格式日期轉換為null

看如下記錄，一個能取到值，一個取不到值**
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，查看報錯信息：

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>
報警說是Incorrect datetime value: ‘20150922 12:55:00’ for function str_to_date，不正確的日期格式，所以換成比較規范的日期格式

原blog地址：http://blog.csdn.net/mchdba/article/details/48719765，未經過csdn原博客博主mchdba允許，不能轉載。

3，換成規范的%Y-%m-%d %H:%i:%s試試

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>

看到在規范的格式下，日期從字符串轉換到日期格式，然後截取時分都是能取到值的。

參考官網地址：http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date

4，再次確定縮小范圍，是%h的問題，將時換成大寫就搞定了

再看到一個案例，將格式變成2015-09-22 13:00:01表准備格式，可以仍然取不到時分值，如下所示：

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> 

那麼問題在哪裡呢？只能再次去官網找答案，找到http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_get-format
看到終極標准格式為：’%Y-%m-%d %H.%i.%s’，換成這個試試，看到范例中的差異在哪裡了嗎？就在於%H和%h的區別啊，如下所示：

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> 

然後標准的日期格式為：%Y%m%d %H:%i:%s’或者%Y-%m-%d %H:%i:%s’ 都可以的，如下所示：

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’);