MySQL日期字符串转换成NULL值的异常处理

mysql 日期格式

目录(?)[+]

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表准备格式，可以仍然取不到时分值，如下所示：

<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-header" style="box-sizing: border-box;">mysql> select DATE_FORMAT(str_to_date('2015-09-22 13:00:01', '%Y-%m-%d %h:%i:%s'),'%h:%i');
+------------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| DATE_FORMAT(str_to_date('2015-09-22 13:00:01', '%Y-%m-%d %h:%i:%s'),'%h:%i') |
+------------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| NULL                                                                         |
+------------------------------------------------------------------------------+</span>
1 row in set, 1 warning (0.00 sec)

<span class="hljs-header" style="box-sizing: border-box;">mysql> select DATE_FORMAT(str_to_date('2015-09-22 12:55:00', '%Y-%m-%d %h:%i:%s'),'%h:%i');
+------------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| DATE_FORMAT(str_to_date('2015-09-22 12:55:00', '%Y-%m-%d %h:%i:%s'),'%h:%i') |
+------------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| 12:55                                                                        |
+------------------------------------------------------------------------------+</span>
1 row in set (0.00 sec)

mysql> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li></ul>

那么问题在哪里呢？只能再次去官网找答案，找到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的区别啊，如下所示：

<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-header" style="box-sizing: border-box;">mysql> select DATE_FORMAT(str_to_date('20150922 13:00:01', '%Y%m%d %h:%i:%s'),'%h:%i');
+--------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| DATE_FORMAT(str_to_date('20150922 13:00:01', '%Y%m%d %h:%i:%s'),'%h:%i') |
+--------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| NULL                                                                     |
+--------------------------------------------------------------------------+</span>
1 row in set, 1 warning (0.00 sec)

<span class="hljs-header" style="box-sizing: border-box;">mysql> select DATE_FORMAT(str_to_date('20150922 12:55:00', '%Y%m%d %h:%i:%s'),'%h:%i');
+--------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| DATE_FORMAT(str_to_date('20150922 12:55:00', '%Y%m%d %h:%i:%s'),'%h:%i') |
+--------------------------------------------------------------------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| 12:55                                                                    |
+--------------------------------------------------------------------------+</span>
1 row in set (0.00 sec)

mysql> </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li></ul>

然后标准的日期格式为：%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’);