Re: [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.
Example:
ApptDate~ApptTime
2005-11-02~01:00 PM
2005-10-27~07:00 PM
2005-06-25~10:30 AM
-00-00~N/A
-00-00~N/A
-00-00~N/A
-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 -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
RE: [PHP-DB] MySQL date casting..
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 -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: -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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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. Example: ApptDate~ApptTime 2005-11-02~01:00 PM 2005-10-27~07:00 PM 2005-06-25~10:30 AM -00-00~N/A -00-00~N/A -00-00~N/A -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 -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
Re: [PHP-DB] MySQL date casting..
Ahh! Thank you Philip! That's what I was looking for! I see what I did wrong
now.
I was using the date format strings wrong. I was using it like I'd use it for
DATE_FORMAT() instead of as an input filter.
This is what I was trying to do:
select STR_TO_DATE('2003-11-05 06:00 PM', '%Y-%m-%d %H:%i:%s')
duh... hah. Thanks! I knew it was something simple.
-TG
--
What's wrong with [using] 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)
___
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
Re: [PHP-DB] MySQL date casting..
Youcould also try CASTing
(http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html) the data
Bastien
From: [EMAIL PROTECTED]
To: php-db@lists.php.net
CC: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] MySQL date casting..
Date: Tue, 17 Jan 2006 12:51:44 -0500
Ahh! Thank you Philip! That's what I was looking for! I see what I did
wrong now.
I was using the date format strings wrong. I was using it like I'd use it
for DATE_FORMAT() instead of as an input filter.
This is what I was trying to do:
select STR_TO_DATE('2003-11-05 06:00 PM', '%Y-%m-%d %H:%i:%s')
duh... hah. Thanks! I knew it was something simple.
-TG
--
What's wrong with [using] 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)
___
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
