* Ben Ramsey
> My hosting company uses MySQL 3.23.58, and I'm running PHP-Nuke 6.9 (if
> you're not familiar with it; it's at http://www.phpnuke.org/).
>
> At any rate, the problem I'm having is that PHP-Nuke uses a field to
> store a "signed-up" date for each user, but, for some reason, it's just
> a text field and not a datetime field. Then, in a members list view,
> you're able to sort by the signed-up date. However, it's sorting them
> in alphabetical order rather than date order (So, a date with "Dec 08
> 2003" is listed before "Jan 22 2004," which is listed before "Sep 18
> 2002"). It just doesn't make any sense to a user viewing it.
>
> Rather than modify the database, though, I would like to use in my SQL
> statement something like the STR_TO_DATE() function so that I can just
> go ahead and do "SELECT STR_TO_DATE(registered_date, '%b %d, %Y') AS
> real_date FROM nuke_users ORDER BY real_date ASC;"
>
> The problem is that my version of MySQL doesn't support this function.
> It's introduced in version 4.1.1. So, is there another way of achieving
> this same result through a SQL statement in 3.23.58?
Yes, it can be done using the FIELD() function:
SELECT * FROM tablename
ORDER BY
MID(datecol,8), # year
FIELD(LEFT(datecol,3), # month
'Jan','Feb','Mar','Apr','May','Jun',
'Jul','Aug','Sep','Oct','Nov','Dec'),
MID(datecol,5,2); # day
<URL: http://www.mysql.com/doc/en/String_functions.html#IDX1185 >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
