Posted by Steve on 01/30/07 18:05

"javelin" <google.1.jvmail@spamgourmet.com> wrote in message
news:1170177671.179606.263210@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').

[Back to original message]