Found at http://lists.mysql.com/mysql/225525 that "hsv" wrote on 12 Aug 2011:
> Function TIME yields a string, not any timestamp type, but "d" is of some
> such type.
> Unhappily, the HTML help that I downloaded is of no help in this case, when
> the result
> context has no type, unless it is supposed that one stops at the first that
> matches:
> ...
> but it looks as if the string is coerced to "d" s type. But since if empty
> string is
> concatenated to the TIME-result it is as you wish, and the value of TIME(d) =
> DATE_FORMAT(d, '%T') is 1, it seems to me an optimizer error.
Thanks for your time reading my message, but I don't understand what
is the "result context" that you are talking about. Could you please
elaborate?
Please notice that the DATE_FORMAT misusage was not a part of the
problem, the IF( DATE(d) = "some-date", TIME(d), d ) was. That is step
#3:
-- 1) create test table containing a single DATETIME column
CREATE TABLE dt (d DATETIME);
-- 2) insert two test records
INSERT INTO dt VALUES ("2011-08-07 10:11:12"),("1234-05-06 07:08:09");
-- 3) THE test: can mysql properly select date/time in an IF?
SELECT d, IF( DATE(d)="2011-08-07", TIME(d), d) x FROM dt;
-- PLEASE notice the WRONG time on the first row
+---------------------+---------------------+
| d | x |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | 2010-11-12 00:00:00 |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
-- 4) as proved by:
SELECT DATE(d), TIME(d) from dt;
+------------+----------+
| DATE(d) | TIME(d) |
+------------+----------+
| 2011-08-07 | 10:11:12 |
| 1234-05-06 | 07:08:09 |
+------------+----------+
What am I doing wrong?!
Thanks again.
++ Please keep a CC to my email address when replying. Thanks.
-ab
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
