List:General Discussion« Previous MessageNext Message »
From:(Hal Date:August 12 2011 2:15am
Subject:Re: Extraneous warning 1292 (Incorrect datetime value)
View as plain text  
>>>> 2011/08/07 18:20 +0300, Marius Feraru >>>>
Would someone please shed some light on what's wrong with calls like
    IF( DATE(d) = "some-date", TIME(d), d )
on DATETIME columns?
Thank you.

I run some tests on mysql 5.0, 5.1 and 5.5: got the same wierd results
everywhere, so I guess I'm missing something, but what is it?
It looks like the engine is trying to use the result of TIME(d) as "d"
in the DATE(d) test, but I can't understand why.
<<<<<<<<
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:
             Expression                        Return Value
expr2 or expr3 returns a string                string
expr2 or expr3 returns a floating-point value  floating-point
expr2 or expr3 returns an integer              integer
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.

As for the warning, your arguments to "DATE_FORMAT" are backwards, and corrected that
yields the outcome that you seek.

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