On 1/27/2012 12:09 PM, John Heim wrote:
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)
In 5.0 use TIME_FORMAT(). In 5.5 and 5.6, DATE_FORMAT() accepts time
values without dates.
PB
-----
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
