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.