Posted by javelin on 01/30/07 17:21

I have a table with dates stored in a text field (designed before my
time, I'm just stuck with the headaches). When I run the following
query:

SELECT DATE_FORMAT(datefield,'%m/%d/%Y') FROM computers where
datefield>'1/1/2000' and DATE_FORMAT(datefield,'%m/%d/%Y') <
'12/1/2006' group by datefield;

I get 22 records. However, when I use the following query (which I
think is better), I lose some of the records that match the criteria,
as when I run the first one:

SELECT DATE_FORMAT(datefield,'%m/%d/%Y') FROM computers where
DATE_FORMAT(datefield,'%m/%d/%Y') >'1/1/2000' and
DATE_FORMAT(datefield,'%m/%d/%Y') < '12/1/2006' group by datefield;

Any clues as to why? Any way to convert the date for accurate
comparisons????

Thanks.

[Back to original message]