Dates have a special data type for a reason - Now... at the end... do you understand...
One way to hack this is to use the "old school date maneuver". Dates will order correctly using text methods if they are in the format yyyy-mm-dd. So if you can't find a way to convert that column (which I highly recommend) you can do something like this (pseudo code) SELECT `abandon`.*, substring(`abandon`.date,7,2) + '-' + substring(`abandon`.date,1,2) + '-' + substring(`abandon`.date,4,2) as SORTDATE FROM `abandon` order by SORTDATE That probably wont work as it is, but I think you'll get the idea Rob On Tue, 14 Dec 2004 10:16:03 -0500, Mike <[EMAIL PROTECTED]> wrote: > A little background: > > Last year I wrote a template for my work to log inbound phone calls. > Its pretty simple, just a web form that takes a date (in the format of > MM-DD-YY), and a few call related numbers. > > Then I display the output in a table using > > SELECT * FROM `abandon` order by DATE (I have date stored in mysql as > a varchar) > > Now, the problem is when we first started there wasn't that many > entries. Now that we're seeing some of the same dates in 04 as we had > in 03 the ORDER BY DATE isn't sorting them accuratly. > > For example: > > 10-22-03 > 10-22-04 > 10-23-03 > 10-23-04 > 10-24-03 > 10-24-04 > 10-25-03 > 10-25-04 > > Any ideas? I considered converting the VARCHAR to DATE, but MYSql > doesn't seem to convert all of the dates correctly.. :( > > -Mike > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187545 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
