-
1. Re: Interesting issue with MySQL Dates
bregent Mar 17, 2010 2:58 PM (in response to Mike_Watt)Seems like a bug as you are sorting by the column name, not the alias. You could try adding another column formatted with the full date and sort by its alias. Not sure if that would work.
-
2. Re: Interesting issue with MySQL Dates
The_FedEx_Guy Mar 18, 2010 9:21 AM (in response to bregent)would an explode function work?
-
3. Re: Interesting issue with MySQL Dates
Mike_Watt Mar 18, 2010 4:30 PM (in response to bregent)I'll give that a shot... pull the full date in the query, and don't display it, just use it for sorting. The other thing I thought of is calling the full date in the query, and format the display in PHP, instead. Like:
SELECT `date` FROM events
Then, in PHP:<?php echo date("j/d", strtotime($row_events['date'])) ;?>
I can't test it right now, but I think that would work, no?
-
4. Re: Interesting issue with MySQL Dates
David_Powers Mar 19, 2010 8:57 AM (in response to Mike_Watt)Mike_Watt wrote:
SELECT DATE_FORMAT(`date`, '%m/%d') AS Date
In SQL, this provides me with 03/17. My problem is that when I then do ORDER BY date, start_time ASC it is only considering the day/month when ordering the results.SELECT DATE_FORMAT('date', '%m/%d') AS formatted_date ORDER BY date, start_time ASC -
5. Re: Interesting issue with MySQL Dates
Mike_Watt Mar 19, 2010 6:19 PM (in response to David_Powers)David:
Is your point that "AS Date" is the problem, and that if I change it to something else (formatted_date) I should be okay? I saw several highlighted parts of your query, but most of them match what I'd already done.
EDIT: I was playing around with it last night and got it to work with this:SELECT DATE_FORMAT(`date`, '%m/%d') AS Date, DATE_FORMAT(start_time, '%l:%i%p') AS Time, DATE_FORMAT(end_time, '%l:%i%p') AS End, title AS Event, id, date AS f_date
FROM events
WHERE `date` >= curdate()
ORDER BY f_date, start_time ASC
But from what I'm getting from your post, David, is that I've basically just created an extra, unnecessary step, is that correct? In this case, if I edited the above SQL to read the following it would be [more] correct(?) :
SELECT DATE_FORMAT(`date`, '%m/%d') AS f_date, DATE_FORMAT(start_time, '%l:%i%p') AS Time, DATE_FORMAT(end_time, '%l:%i%p') AS End, title AS Event, id
FROM events
WHERE f_date >= curdate()
ORDER BY date, start_time ASC
-
6. Re: Interesting issue with MySQL Dates
David_Powers Mar 21, 2010 4:46 AM (in response to Mike_Watt)Mike_Watt wrote:
Is your point that "AS Date" is the problem, and that if I change it to something else (formatted_date) I should be okay? I saw several highlighted parts of your query, but most of them match what I'd already done.
The highlighting is simply the way this forum highlights SQL. I think the idea is that keywords are highlighted.
Yes, you have got the basic idea. You're creating an alias to hold a different value, so when you reassigned the value of the date column to Date, you ended up with the order being based on the reformatted value. On a Linux server, date and Date would be treated differently, but Windows is case-insensitive.
Your final version is fine, except that you should compare date, rather than f_date, with curdate().




