List:General Discussion« Previous MessageNext Message »
From:Marius Feraru Date:August 7 2011 3:20pm
Subject:Extraneous warning 1292 (Incorrect datetime value)
View as plain text  
Hello
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.

Here's a runnable test and its output:

$ mysql test -vve 'DROP TABLE IF EXISTS dt; SET @rd="2011-08-07",
@rt="10:11:12"; CREATE TABLE dt (d DATETIME); INSERT INTO dt VALUES
(CONCAT_WS(" ",@rd,@rt)),("1234-05-06 07:08:09"); SELECT d, IF(
DATE(d)=@rd, TIME(d), d) wtf1 FROM dt; SELECT d, IF( DATE(d)=@rd,
DATE_FORMAT("%T",d), d) wtf2 FROM dt; SELECT d, IF( DATE(d)=@rd,
REPLACE(d,CONCAT(@rd," "),""), d) z FROM dt;'
--------------
DROP TABLE IF EXISTS dt
--------------

Query OK, 0 rows affected (0.05 sec)

--------------
SET @rd="2011-08-07", @rt="10:11:12"
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE dt (d DATETIME)
--------------

Query OK, 0 rows affected (0.16 sec)

--------------
INSERT INTO dt VALUES (CONCAT_WS(" ",@rd,@rt)),("1234-05-06 07:08:09")
--------------

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

--------------
SELECT d, IF( DATE(d)=@rd, TIME(d), d) wtf1 FROM dt
--------------

+---------------------+---------------------+
| d                   | wtf1                |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | 2010-11-12 00:00:00 |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

--------------
SELECT d, IF( DATE(d)=@rd, DATE_FORMAT("%T",d), d) wtf2 FROM dt
--------------

+---------------------+---------------------+
| d                   | wtf2                |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | NULL                |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
2 rows in set, 1 warning (0.01 sec)

Warning (Code 1292): Incorrect datetime value: '%T'
--------------
SELECT d, IF( DATE(d)=@rd, REPLACE(d,CONCAT(@rd," "),""), d) z FROM dt
--------------

+---------------------+---------------------+
| d                   | z                   |
+---------------------+---------------------+
| 2011-08-07 10:11:12 | 10:11:12            |
| 1234-05-06 07:08:09 | 1234-05-06 07:08:09 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

Bye


-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