Inconsistent ColdFusion/MySql results

[lrempel]

i have a mysql query which i have written in a coldfusion template. when i test it on my local machine (running CFMX and MySql 4.1.5-gamma-nt) it works perfectly. when i upload it onto my host's server (running CFMX and MySql 4.0.15-nt) and test it there, it doesn't work in the same way (i'll explain the details below). however, i also tested it through my hosts phpMyAdmin interface (i thought it might be the different MySql versions which was causing the problem) and it works perfectly.

Here's my query:

SELECT DISTINCT fld_fname, fld_lname, min(fld_dte) AS fromDte, max(fld_dte) AS toDte
FROM tbl_athletes, tbl_events
WHERE tbl_events.fld_dte BETWEEN DATE '2005-01-01' and DATE '2005-06-01'
AND tbl_athletes.fld_athlete_id = tbl_events.fld_athlete_id
GROUP BY fld_lname, fld_fname

on my local machine and the phpMyAdmin interface the results for fromDte and toDte come out looking like dates. on my host's server however, fromDte and toDte come out as a bunch of numbers - it says it's binary format.

i can't figure out why this is happening, but i know it has to do with the max and min, because just selecting all the fld_dte results works fine (it gives them in date format). the problem is, i really need to only get the oldest and most recent dates in my results, and i can't think of another way to do this.

so if you can tell me what i'm doing wrong (or why things are happening the way they are) or else suggest a different way of achieving the results i want, i would appreciate it.

thanks.

[davidjmedlock]

Are the versions of ColdFusion MX the same? Maybe there's a driver on your server that's not playing nice with the version of CFMX on your server... Could be that version of MySQL, too.

[r937]

the use of the DATE keyword is unusual, i think that's standard sql but i rarely see it, i know the query will work in mysql without it

fld_dte is a DATE or DATETIME datatype?

also, you don't need DISTINCT here

[lrempel]

they are the same versions of ColdFusion MX.

fld_dte is a date datatype - i don't need or want time info.

thanks for the tip about DISTINCT - i had it in before i was using min/max

anyways - i removed the DATE keyword in my BETWEEN clause, and still the same thing happened - the result gets converted to a binary datatype, which i can't work with because i need them to be dates!

any suggestions of a different way of achieving the same results? i'm open to changing my query. any other help you can provide would be good, i'm kinda stuck here...

thanks

[r937]

how about forcing the min/max to operate on strings instead? unfortunately this means that you would have to see the results in yyyy-mm-dd format

select fld_fname
     , fld_lname
     , min(date_format(fld_dte,'%Y-%m-%d')) as fromDte
     , max(date_format(fld_dte,'%Y-%m-%d')) as toDte
  from ...

[lrempel]

r937,

i tried the code you posted, but the results are exactly the same - fromDte and toDte come out as binary. it was a good idea, i don't mind working with dates in yyyy-mm-dd format, that's how they are in the database.

any other thoughts? anyone who might know WHY this is happening feel free to jump in. i still don't know if it is a mysql or coldfusion problem, or a combination of the two.

thanks for the suggestions so far.