| Philip Hallstrom 2006-01-17, 6:57 pm |
| > 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)
|