微信赛车群二维码-武汉什么医院做人流比较好的医院

首页

AD联系:507867812

微信赛车群二维码

时间:2019-11-17 12:23:42 作者:万博体育在线 浏览量:94116

微信赛车群二维码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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成NULL值的异常处理,见下图

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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

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

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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

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

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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成NULL值的异常处理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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

微信赛车群二维码MySQL日期字符串转换成NULL值的异常处理

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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

1.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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

MySQL日期字符串转换成NULL值的异常处理MySQL日期字符串转换成NULL值的异常处理MySQL日期字符串转换成NULL值的异常处理MySQL日期字符串转换成NULL值的异常处理MySQL日期字符串转换成NULL值的异常处理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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成NULL值的异常处理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,不正确的日期格式,所以换成比较规范的日期格式

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

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

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

MySQL日期字符串转换成NULL值的异常处理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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

3.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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

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

4.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,不正确的日期格式,所以换成比较规范的日期格式

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

linux1,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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linuxMySQL日期字符串转换成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,不正确的日期格式,所以换成比较规范的日期格式

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

linux。微信赛车群二维码

展开全文
相关文章
江苏骰宝

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux

ag8829环亚娱乐

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux....

qy8千亿国际手机版

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

澳门赌场haobc

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux....

相关资讯
dafa888

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux....

pk10百万系统

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,不正确的日期格式,所以换成比较规范的日期格式

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

linux....

热门资讯