Forgive me that this isn't really PHP related, but solely MySQL.. but the MySQL mailing lists drive me nuts and figured someone here would have a quick answer. I'm trying to sort by a date and time field(s) (two separate fields). It's a dumb system but until we do the next revision, it's going to stay the way it is (boss' orders) so bear with me. Example: ApptDate~ApptTime 2005-11-02~01:00 PM 2005-10-27~07:00 PM 2005-06-25~10:30 AM 0000-00-00~N/A 0000-00-00~N/A 0000-00-00~N/A 0000-00-00~06:30 PM See? Dumb.. hah.. So I thought I could do something like this: select ApptDate, ApptTime, DATE_FORMAT(CONCAT(ApptDate, ' ', ApptTime), '%Y-%m-%d %H:%i:%s') from Table But it doesn't like "06:00 PM".. returns null on the items that have a valid date and time because the time format isn't what it wants. If I try it with a "06:00:00 PM" time, it makes it 6am. Using STR_TO_DATE() does exactly the same thing. You'd think STR_TO_DATE() would behave more like PHP's strtotime() but apparently not. I can code a big complicated conditional SQL statement, but I'm hoping there's a way to convert at least the valid date/time pairs into a happily ORDER BY'd column. I can handle the 0000-00-00 and N/A entries with exceptions if I need to. And I would really like to do this without pre-loading the data into PHP and sorting it with PHP's sort functions. Any MySQL gurus who can show me what I'm missing here? Thanks in advance! -TG ___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
