DATE() equality not working for some rows

[dlite922]

Hey guys,

Sometimes it's the most simplest things in life that drive you insane. I'm no complete MySQL noob and I'm not doing anything fancy by any stretch.

My table contains two date-times:
1. original_date
2. begin_date

I'd like to select rows where original date is equal to begin date and the date ONLY (not the time). I'd also would like only rows who's name start with a prefix such as "FOO%" or "BAR%". Easy, right?

(Actually my query is slightly more complex than this, but I've reduced the problem to this)

Here's the simple query:

SELECT 
   name,
   original_date,
   begin_date
FROM
   t
WHERE
   DATE(original_date) = DATE(begin_date) AND
   name LIKE 'TEST%';

The Problem:
The above query works for some rows and not others, i.e. with a certain prefix I get an Empty set. When I remove the Date equality I get results back.

Now you're thinking: "Well maybe there aren't any records where the dates are equal"

Wrong. Unless my eyes are deceiving me, I see dates that are equal in both columns. I put back the date clause and get empty set.

What's even MORE frustrating is this seems to work for other rows just by simply changing the prefix, i.e. selecting other rows.

I've also tried replacing the equality clause with a hard coded date that I see in the unconstrainted record set, such as:

WHERE DATE(original_d ate) = '2010-10-20'

Dying to see some data now?

Here you go: (begin_time converted GMT -> EDT)

SELECT name, DATE_SUB(begin_time, INTERVAL 4 HOUR), original_air_date FROM event WHERE  name LIKE "NHL:%";

.
.
.
| NHL: Flyers at Penguins            | 2010-10-30 12:00:00                   | 2010-10-29 00:00:00 |
| NHL: Rangers at Maple Leafs        | 2010-10-30 19:00:00                   | 2010-10-30 00:00:00 |
| NHL: Avalanche at Rangers          | 2010-10-19 04:00:00                   | 2010-10-18 00:00:00 |
| NHL: Islanders vs Lightning        | 2010-10-22 02:00:00                   | 2010-10-21 00:00:00 |
| NHL: Devils vs Canadiens           | 2010-10-22 07:30:00                   | 2010-10-21 00:00:00 |
| NHL: Islanders vs Lightning        | 2010-10-22 12:00:00                   | 2010-10-21 00:00:00 |
| NHL: Devils vs Canadiens           | 2010-10-22 14:00:00                   | 2010-10-21 00:00:00 |
| NHL: Sabres vs Devils              | 2010-10-23 19:00:00                   | 2010-10-23 00:00:00 |
| NHL: Rangers vs Maple Leafs        | 2010-10-22 01:00:00                   | 2010-10-21 00:00:00 |
| NHL: Rangers vs Maple Leafs        | 2010-10-22 10:00:00                   | 2010-10-21 00:00:00 |
| NHL: Rangers vs Maple Leafs        | 2010-10-22 14:00:00                   | 2010-10-21 00:00:00 |
| NHL: Rangers vs Bruins             | 2010-10-23 19:00:00                   | 2010-10-23 00:00:00 |
| NHL: Capitals vs Bruins            | 2010-10-22 14:00:00                   | 2010-10-21 00:00:00 |
| NHL: Rangers vs Bruins             | 2010-10-23 19:00:00                   | 2010-10-23 00:00:00 |
| NHL: Capitals vs Bruins            | 2010-10-22 00:00:00                   | 2010-10-21 00:00:00 |
| NHL: Capitals vs Bruins            | 2010-10-22 14:00:00                   | 2010-10-21 00:00:00 |
| NHL: Rangers vs Bruins             | 2010-10-23 19:00:00                   | 2010-10-23 00:00:00 |
| NHL: Capitals vs Bruins            | 2010-10-22 00:00:00                   | 2010-10-21 00:00:00 |
| NHL: Wild vs Canucks               | 2010-10-22 22:00:00                   | 2010-10-22 00:00:00 |
+------------------------------------+---------------------------------------+---------------------+
495 rows in set (2.93 sec)

You can see there are not a lot but a few in this subset I pasted that have the same date. Look at the third from the bottom. Both are 2010-10-23 right?

Watch this now:

// I added the clause: begin_time in Eastern = original date GMT: 

SELECT 
   name, 
   DATE_SUB(begin_time, INTERVAL 4 HOUR), 
   original_air_date 
FROM event 
WHERE  
   name LIKE "NHL:%" AND 
   DATE(original_air_date) = DATE(DATE_SUB(begin_time, INTERVAL 4 HOUR));
Empty set (2.89 sec)






   DATE(original_air_date) = '2010-10-23';  <<--- Tried it like this too:
Empty set (2.91 sec)

Is there a MySQL bug? To keep this post short I didn't post my result for another prefix "NFL:%" which doesn't "appear" to have this problem.

Can anyone guess what's going on? Throw anything at me. At this point I'm desperate and out of ideas.

Thanks Much,

Dan


UPDATE: It appears some of the dates for NHL are NULL while none are NULL for NFL. Could NULL values be throwing this off? That's the only difference I've found so far.

[code green]

The dates will not be equal if there is a time element in there.
So the DATETIME data type needs casting to DATE.
To compare to a field containing NULL values will produce unpredictable results.
Get around this by using COALESCE() which returns the first none NULL value i.e.

(DATE(COALESCE(original_date,'1970-01-01')) = DATE(COALESCE(begin_date,'1970-01-01')) or something like that

[JKing]

Were you able to resolve this Dan?