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!
What's wrong with this?
mysql> select str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p');
+---------------------------------------------------------+
| str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p') |
+---------------------------------------------------------+
| 2005-10-27 19:00:00 |
+---------------------------------------------------------+
1 row in set (0.04 sec)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
