Date comparison issue

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.

.

Relevant Pages

  • Re: Access to a custom table
    ... Create a collection based on this query for machines you want to include: ... "Tony" wrote: ... Let say I have computers named "A, B, C, D" in the database. ... > that has the NOT subselect query. ...
    (microsoft.public.sms.admin)
  • RE: How to combine existing collections to include or exclude...
    ... "Matthew Hudson" wrote: ... Servers you want minus the ones you don't want ... Add computers you don't want to show up in Collection B ... will see if I can't just send you the collection queries and the query ...
    (microsoft.public.sms.admin)
  • Re: How to combine existing collections to include or exclude...
    ... "Matthew Hudson" wrote: ... Servers you want minus the ones you don't want ... [edit query statement/Criteria] ... Add computers you don't want to show up in Collection B ...
    (microsoft.public.sms.admin)
  • RE: How to combine existing collections to include or exclude...
    ... is selecting the computers in my remote offices via AD but limits it only to ... select the query and say "computer name" not it limited ... This will grab all the computers that are in the limited collected minus the ...
    (microsoft.public.sms.admin)
  • RE: How to combine existing collections to include or exclude...
    ... is selecting the computers in my remote offices via AD but limits it only to ... then edit the collection and do a query "Sub selected values" you can then ... This will grab all the computers that are in the limited collected minus the ...
    (microsoft.public.sms.admin)