请高手帮忙看一下MySQL自定义函数为什么没有返回结果？

Question

自定义函数脚本CREATE DEFINER=`root`@`%` FUNCTION `to_ymd`(`datetime` char(19)) RETURNS date
BEGIN
#Routine body goes here...
RETURN str_to_date(datetime, '%m/%d/%Y');
END
调用该函数select to_ymd('12/21/2010 13:55:37');
可是结果为空，应该返回2010-12-21才对。

Answer 1

用你的代码测试如下，没有你所说的问题。mysql> CREATE FUNCTION `to_ymd`(`datetime` char(19)) RETURNS date
    ->     RETURN str_to_date(datetime, '%m/%d/%Y');
Query OK, 0 rows affected (0.13 sec)mysql>
mysql> select to_ymd('12/21/2010 13:55:37');
+-------------------------------+
| to_ymd('12/21/2010 13:55:37') |
+-------------------------------+
| 2010-12-21                    |
+-------------------------------+
1 row in set, 1 warning (0.03 sec)mysql>

Answer 2

mysql> select to_ymd('02/12/2011 12:15:41');
Empty set

Answer 3

show create function to_ymd; 贴出来看一下。

Answer 4

mysql> show create function to_ymd;
+----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode                                                       | Create Function                                                                                                                                       | character_set_client | collation_connection | Database Collation |
+----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| to_ymd   | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `to_ymd`(`dt` char(19)) RETURNS date
BEGIN
#Routine body goes here...
RETURN str_to_date(`dt`,'%m/%d/%Y');
END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in setmysql> 你的MySQL版本是多少？我已经找到解决办法了，把str_to_date(`dt`,'%m/%d/%Y')改成str_to_date(`dt`,'%m/%d/%Y %H:%i:%s')结果就出来了。

Answer 5

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.52-community |
+------------------+
1 row in set (0.00 sec)mysql>