date comparison problem in mysql 4.0.2

[rravenn]

I have date comparison problem with mysql... why doesn't the date get in the set? (2nd query returns empty set)


mysql> select version();
+------------+
| version() |
+------------+
| 4.0.20-log |
+------------+
1 row in set (0.00 sec)


mysql> SELECT RecordID FROM Record WHERE DATE_ADD(PostDT,INTERVAL 11 HOUR) >= CAST('2005-3-13 00:00:00' AS datetime) AND DATE_ADD(PostDT,INTERVAL 11 HOUR) <= CAST('2005-3-13 23:59:59' AS datetime) ORDER BY PostDT DESC;
Empty set (0.00 sec)

mysql> select DATE_ADD(PostDT,INTERVAL 11 HOUR) FROM Record;
+-----------------------------------+
| DATE_ADD(PostDT,INTERVAL 11 HOUR) |
+-----------------------------------+
| 2005-03-13 12:00:00 |
| 2005-05-27 21:53:30 |
+-----------------------------------+
2 rows in set (0.00 sec)

 

[rravenn]

in fact...

select CAST('2005-3-13 23:59:59' AS datetime) = CAST('2005-03-13 23:59:59' AS datetime)
-> ;
+-----------------------------------------------------------------------------------+
| CAST('2005-3-13 23:59:59' AS datetime) = CAST('2005-03-13 23:59:59' AS datetime) |
+-----------------------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------------------

The only thing I can say is "wtf" (sorry)

 

[TonyGroves]

Looks like a quirk in version 4.0, which has been fixed in 4.1:
[tt]
C:\prog\mysql\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server [/tt][tt]version: 4.1.7-nt[/tt]
[tt]
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select cast('2005-3-13 23:59:59' as datetime);
+----------------------------------------+
| cast('2005-3-13 23:59:59' as datetime) |
+----------------------------------------+
| 2005-03-13 23:59:59 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> quit;
Bye

C:\prog\mysql\bin>mysql -h 192.168.1.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: [/tt][tt]4.0.18-nt[/tt]
[tt]
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select cast('2005-3-13 23:59:59' as datetime);
+----------------------------------------+
| cast('2005-3-13 23:59:59' as datetime) |
+----------------------------------------+
| 2005-3-13 23:59:59 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> quit;
Bye

C:\prog\mysql\bin>
[/tt]

The solution is simple of course - make sure to zero-pad your dates and times.