Unfortunately, no.  The dates and times are stored as text. So here's what I 

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. :)


= = = 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-----
Sent: 17 January 2006 16:42
To: php-db@lists.php.net
Subject: [PHP-DB] 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.


2005-11-02~01:00 PM
2005-10-27~07:00 PM
2005-06-25~10:30 AM
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!


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

Reply via email to