Unfortunately, no. The dates and times are stored as text. So here's what I get: 2006-01-10 07:00 PM 2006-01-10 08:00 PM 2006-01-10 09:00 AM 2006-01-10 09:00 PM (notice the "AM" out of order) For anyone interested, here's the big ugly version.. if anyone knows of a function that I can use instead of all this garbage, let me know: select ApptDate, ApptTime, CONCAT(ApptDate, ' ', IF(SUBSTRING(ApptTime, 7, 2) = 'PM', CONCAT(SUBSTRING(ApptTime, 1, 2) + 12, ':', SUBSTRING(ApptTime, 4, 2), ':00'), IF(SUBSTRING(ApptTime, 7, 2) = 'AM', CONCAT(SUBSTRING(ApptTime, 1, 2), ':', SUBSTRING(ApptTime, 4, 2), ':00'), '00:00:00'))) AS ProperDateTime from SomeTable I'm not worried about blank or 0000-00-00 type dates in this case so don't really account for them. And again, this is meant to work on separate date and time fields in the following formats: ApptDate: YYYY-MM-DD (we at least did that part 'normal') ApptTime: HH:MM [AM|PM] Thanks for the response though Stephen... always worth going over the simple stuff first. :) -TG = = = Original message = = = Could you not just add: ORDER BY ApptDate, ApptTime To the end of your SQL - this will order the set by date then time. You can also add ASC or DESC for Ascending or Dscending as desired. N.B. the ORDER BY .... statement must be the last part of you query! -----Original Message----- From: tg-php@xxxxxxxxxxxxxxxxxxxxxx [mailto:tg-php@xxxxxxxxxxxxxxxxxxxxxx] Sent: 17 January 2006 16:42 To: php-db@xxxxxxxxxxxxx Subject: MySQL date casting.. 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
