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.

"javelin" <google.1.jvmail [at] spamgourmet.com> wrote in message
news:1170177671.179606.263210 [at] a75g2000cwd.googlegroups.com.. .
|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????

don't use date_format. otherwise, you are comparing a string to a string
rather than a date to a date that mysql translates/converts from a string
(ex. '1/1/2006').

Post removed (X-No-Archive: yes)