I'm trying to create a function that formats a time in a standard way ('%H:%i'). But all I can seem to get back is null.

DROP TABLE IF EXISTS bogus_table;
CREATE TABLE IF NOT EXISTS bogus_table (
btime TIME
);

INSERT INTO bogus_table VALUES ('12:34:56');

DROP FUNCTION IF EXISTS bogus ;
DELIMITER $$
CREATE FUNCTION bogus (
btime TIME
)
  RETURNS VARCHAR(10)
BEGIN
DECLARE ctime VARCHAR(10) DEFAULT '';
SET ctime=DATE_FORMAT(btime,'%H:%i');
RETURN ctime;
END $$
DELIMITER ;

SELECT btime,
bogus(btime) AS btime1,
DATE_FORMAT(btime,'%H:%i') AS btime2
FROM bogus_table;

+----------+--------+--------+
| btime    | btime1 | btime2 |
+----------+--------+--------+
| 12:34:56 | NULL   | 12:34  |
+----------+--------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '12:34:56' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

Reply via email to