Re: MySQL date casting..

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux