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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org