brady.k — Fri, 09 Apr 2004 20:34:36 +0000

lol thx

Suzanne — Fri, 09 Apr 2004 20:16:53 +0000

<?php
$sql = mysql_query(\"SELECT TIME_FORMAT(time, '%l:%i %p') AS time, activity, qual, place,  
DATE_FORMAT(date, '%m/%d/%Y') AS date FROM loyola WHERE date >= NOW() ORDER BY date\") 
or die(mysql_error());
?>

That works for me as intended. All these options, eh?

brady.k — Fri, 09 Apr 2004 19:33:31 +0000

ok...i'll try it with TIME_FORMAT and see how it goes

Suzanne — Fri, 09 Apr 2004 19:19:47 +0000

I'm never answering questions late at night anymore, lol... Sorry!

Quote:
TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'

I don't think you can use DATE_FORMAT on just TIME.

brady.k — Fri, 09 Apr 2004 14:37:53 +0000

<?php
DATE_FORMAT(time, '%l:%i %p') AS time
?>

That's in the original sql query...the online doc says that should transform it between AM and PM

Suzanne — Fri, 09 Apr 2004 03:54:59 +0000

Seems like midnight and noon are being reported identically, as well.

I did a quick test on my local server and didn't get the same results with your data as you're getting. How are you transforming the time to report AM and PM and such?

Edit to add file of db structure. I didn't pay attention to the other fields, just the ones in question.

brady.k — Fri, 09 Apr 2004 03:11:11 +0000

no spaces, no weird characters, ...thats why I posted. It doesn't make sense.

Heres a dump of the data:

id activity qual place time date
1 Loyola vs. Gilman Diving I Gilman 16:00:00 2004-11-30
2 Loyola vs. LaSalle, Malvern Modified Diving Loyola 12:00:00 2004-12-04
3 Loyola vs. JC, St. P & P Varsity Only, Diving II Loyola 15:30:00 2004-12-07
4 Loyola vs. Curley Varsity Only, Diving IV Loyola 15:30:00 2004-12-14
5 Loyola vs. Severn Diving V Loyola 15:30:00 2005-01-04
6 Loyola vs. McDonough Diving III Loyola 15:30:00 2005-01-11
7 Loyola vs. Mt. St. Joe Diving I Mt. St. Joe 15:30:00 2005-01-25
8 National Catholics Qualifiers Only Villanova Univ. 00:00:00 2005-01-29
9 National Catholics Qualifiers Only Villanova Univ. 00:00:00 2005-01-30
10 Loyola vs. Calvert Hall Diving IV Calvert Hall 15:30:00 2005-02-08
11 MIAA Championships (1) Pre-lims Loyola 09:00:00 2005-02-12
12 MIAA Championships (2) Finals Loyola 18:00:00 2005-02-12
13 MIAA Championships Diving Loyola 12:00:00 2005-02-12
14 Easterns Qualifiers Only LaSalle 00:00:00 2005-02-25
15 Easterns Qualifiers Only LaSalle 00:00:00 2005-02-26

Suzanne — Fri, 09 Apr 2004 01:07:30 +0000

(sorry, I gave you information you already know on the time, I waited too long to post)

Suzanne — Fri, 09 Apr 2004 01:05:16 +0000

you might want to check the data types for that field and do a test on inserting the values, illegal values are probably illegal characters or wrong spacing so the time doesn't make sense.

Quote: Illegal TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a legal TIME value, there is no way to tell, from a value of '00:00:00' stored in a table, whether the original value was specified as '00:00:00' or whether it was illegal.

Make sure there are no spaces in the TIME fields?

brady.k — Fri, 09 Apr 2004 00:50:10 +0000

Well after reading that...it seems to think that my time values are illegal...but they aren't

Example: 16:00:00 [4 PM]......14:00:00 [2 PM]

All of the times are legal....

About the ASC/DESC...I didn't know MySQL had online doc...thanks.