From: Peter Brawley
Date: January 27 2012 8:19pm
Subject: Re: cannot pass time to function
List-Archive: http://lists.mysql.com/mysql/226691
Message-Id: <4F2306D3.9040604@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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