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>
>
