Something like STR_TO_DATE in 3.23.58?
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? Thanks, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Something like STR_TO_DATE in 3.23.58?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Works perfectly! Thanks! Roger Baklund wrote: | 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 | | http://www.mysql.com/doc/en/String_functions.html#IDX1185 > | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAEABdIcIs3CP5DxwRAl8rAJ91Di2n0/n/uuSBSAm0dJle+GIXUACdENd2 C8NmS3Ft2iIlrad6BRFA7wE= =kVtA -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Something like STR_TO_DATE in 3.23.58?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Actually, it's not working quite as perfect as I thought. When using ASC, it works fine, but when using DESC for sort order, it's quirky. For example, in ASC mode, it orders like this: Sep 28, 2003 Oct 05, 2003 Dec 06, 2003 Dec 31, 2003 Jan 02, 2004 Jan 10, 2004 Jan 14, 2004 That's perfect, but in DESC mode, it orders like this: Sep 28, 2003 Oct 05, 2003 Dec 31, 2003 Dec 06, 2003 Jan 14, 2004 Jan 10, 2004 Jan 02, 2004 It just orders the dates descending within their respective months, but it doesn't order DESC by year or month. I figured out how to fix it, though. I just placed DESC after each item listed in the ORDER BY. Ben Ramsey wrote: | Works perfectly! Thanks! | | Roger Baklund wrote: | | 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 | | | | http://www.mysql.com/doc/en/String_functions.html#IDX1185 > | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAEAqlIcIs3CP5DxwRAhh4AJ9p0maCDE+Lc9HM3a3vb5p3XjqLcACeJsx0 1tQS3OslXlmBQITYE57NIHU= =eLqZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Something like STR_TO_DATE in 3.23.58?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yeah, that was my mistake. Sorry about that. I've changed it to 9. Roger Baklund wrote: | * Paul DuBois | [...] | |>>Jan 02, 2004 | | [...] | |>By the way, why do you have MID(datecol,8) rather than MID(datecol,9)? | | | There was no comma in the dates in his original post. He should use | MID(datecol,9) if there actually is a comma in his dates, though | MID(datecol,8) will also work in this case because of the space preceeding | the year. | | -- | Roger | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAEBylIcIs3CP5DxwRAkAJAJ9xpqbZNiFj+56rHC8266/Jg61LVQCggGU0 U2MNvh2up5qNaKZCXn0MwDY= =Nyzr -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
