This question already has an answer here:
Using PHP and MySQL, I have a table of events that are sorted by a field called: sort_date, formatted in yyyy/mm/dd.
I need the display on the page to show a start_date of: Month dd (November 7, for example).
So I had the start_date and end_date fields originally set as a datepicker. But once a record was entered, I had to go in and change the dates manually to be formatted: Month dd. But that didn't sort the dates in ascending order, so I have a Sort_date field...and this is in yyyy/mm/dd format.
I want the event listed like this on my page:
August 27 Time Where Address Description
This is working, but now that I changed the start and end dates to Text, the sort_date field isn't reading the info.
So my question is, How can I have the sort_date field read what is in the TEXT start_date field and show it as yyyy/mm/dd?
Here is my sql statement:
SELECT *
FROM events
WHERE DATE_FORMAT(sort_date, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d')
AND authorize = 1
ORDER BY sort_date
How can I fix this?
</div>
This takes two steps.
First you need to get your text-column date converted to a DATE expression.
You can do that with, for example,
STR_TO_DATE('2014/11/18', '%Y/%m/%d')
Then, you can manipulate that DATE expression, to sort it, or format it, or whatever you need to do. To get November 2014 from the above expression you'd do:
DATE_FORMAT(STR_TO_DATE('2014/11/18', '%Y/%m/%d'), '%M %Y')
To get a DATE value containing the first of November (the first day of the month) from that expression you can do this.
DATE(DATE_FORMAT(STR_TO_DATE('2014/11/18', '%Y/%m/%d'), '%Y-%m-01'))
The point is that you should convert your string-based dates to DATE expressions, because then all kinds of useful date arithmetic operations available.
To handle the WHERE clause in your question, you do this:
WHERE STR_TO_DATE(sort_date, '%Y/%m/%d') >= NOW()
To handle the ORDER BY clause you could do something like this:
ORDER BY STR_TO_DATE(sort_date, '%Y/%m/%d')
There's a hazard here. If some of your date strings are misformatted, MySQL date conversion will silently return NULL. So, I would use the following ORDER BY clause and then look at the output carefully, at least once
ORDER BY STR_TO_DATE(sort_date, '%Y/%m/%d') NULLS FIRST