Re: Date comparison issue
- From: "Steve" <no.one@xxxxxxxxxxx>
- Date: Tue, 30 Jan 2007 12:05:21 -0600
"javelin" <google.1.jvmail@xxxxxxxxxxxxxxx> wrote in message
news:1170177671.179606.263210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|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').
.
- References:
- Date comparison issue
- From: javelin
- Date comparison issue
- Prev by Date: Date comparison issue
- Next by Date: php.ini for every vhost
- Previous by thread: Date comparison issue
- Next by thread: php.ini for every vhost
- Index(es):
Relevant Pages
|
