List:General Discussion« Previous MessageNext Message »
From:Marius Feraru Date:August 18 2011 3:30pm
Subject:Re: Extraneous warning 1292 (Incorrect datetime value)
View as plain text  
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
Thread
Extraneous warning 1292 (Incorrect datetime value)Marius Feraru7 Aug
  • Re: Extraneous warning 1292 (Incorrect datetime value)Dan Nelson7 Aug
    • Re: Extraneous warning 1292 (Incorrect datetime value)Marius Feraru7 Aug
      • Re: Extraneous warning 1292 (Incorrect datetime value)Marius Feraru18 Aug
        • Re: Wrong conversion to timestamp from character stringhsv26 Aug
  • Re: Extraneous warning 1292 (Incorrect datetime value)hsv12 Aug