Posted by Tom on 01/30/07 20:02

On 30 Jan 2007 09:21:11 -0800, javelin wrote...
>
>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.
>

I would guess the only time you really need to worry about the format of your
date information is on the output, or the "select" part of your statement. If
your column is already defined as a date or timestamp type column, then SQL
probably doesn't need the extra formatting to figure out how to sort that. I saw
an earlier post where someone suggested it might be converting to a text string
so that would sort it alphabetically instead of chronologically.

Tom
--
Newsguy.com Basic $39.95 / year
http://www.newsguy.com/overview.htm

[Back to original message]