Dates are not sorting correctly with date_format

Databases
1

I have formatted my date/time column using the following, but it is not sorting the results correctly. Does anyone know why?

date_format(searchDate, ‘%Y-%m-%d %l:%i %p’) as date

Results from the query:

2011-10-04 9:53 AM
2011-10-04 10:38 AM
2011-10-04 10:34 AM

Should I sort the data using the query and then format how the date looks using PHP?

Thanks!

2

DATE_FORMAT produces a string

if you sort on that string, and it contains AM/PM, then you get weird results

go ahead and use DATE_FORMAT on the column to display it the way you want, but in the ORDER BY clause, sort on the unformatted column

3

Unfortunately this is a query that combines rows from three different tables. One table calls is searchDate, another table calls is dateAdded, etc. So I think I’m forced to either sort with this string version, or use PHP to format how the date shows.

What say ye?

4

not much, because i can’t see your actual query

5

SELECT date_format(answerDate, ‘%m-%d-%Y %l:%i %p’) as date
, cID AS ID
, ‘campaign’ AS source
, campaign AS type
, answer AS entry
FROM campaigns
WHERE uID = ‘$uID’
UNION ALL
SELECT date_format(searchDate, ‘%Y-%m-%d %l:%i %p’) as date
, sID as ID
, ‘searches’ AS source
, type
, keyword
FROM searches
WHERE author = ‘$uID’ limit 5
UNION ALL
SELECT date_format(dateAdded, ‘%Y-%m-%d %l:%i %p’) as date
, cID as ID
, ‘communications’ AS source
, type
, entry
FROM communications
WHERE uID = ‘$uID’
UNION ALL
SELECT date_format(payDate, ‘%Y-%m-%d %l:%i %p’) as date
, paymentID as ID
, ‘payments’ AS source
, type
, frequency
FROM payments
WHERE uID = ‘$uID’
ORDER
BY date desc
, source;

6

hm, that query looks vaguely familiar… :wink:


SELECT DATE_FORMAT(answerDate, '%m-%d-%Y %l:%i %p') as date
     , [COLOR="#0000FF"]answerDate AS sort_date[/COLOR]
     , cID AS ID
     , 'campaign' AS source
     , campaign AS type
     , answer AS entry
  FROM campaigns
 WHERE uID = '$uID'
UNION ALL
SELECT DATE_FORMAT(searchDate, '%Y-%m-%d %l:%i %p') as date
     , [COLOR="#0000FF"]searchDate AS sort_date[/COLOR]
     , sID as ID
     , 'searches' AS source
     , type
     , keyword
  FROM searches
 WHERE author = '$uID' [COLOR="#FF0000"]-- limit 5 is invalid here[/COLOR]
UNION ALL
SELECT DATE_FORMAT(dateAdded, '%Y-%m-%d %l:%i %p') as date
     , [COLOR="#0000FF"]dateAdded AS sort_date[/COLOR]
     , cID as ID
     , 'communications' AS source
     , type
     , entry
  FROM communications
 WHERE uID = '$uID'
UNION ALL
SELECT DATE_FORMAT(payDate, '%Y-%m-%d %l:%i %p') as date
     , [COLOR="#0000FF"]payDate AS sort_date[/COLOR]
     , paymentID as ID
     , 'payments' AS source
     , type
     , frequency
  FROM payments
 WHERE uID = '$uID'
ORDER
    BY [COLOR="#0000FF"]sort_date [/COLOR]DESC
     , source;
7

Strange, but the limit 5 actually works for me.

8

Rudy, so what is the best way to limit the number of results from each of the three tables listed in the query above?

9

----> “Strange, but the limit 5 actually works for me.” - busboy

:cool:

10

Well, I guess I need to clarify. Limit five actually limits the results from that table to 5, but I can’t do an order by clause, so therefore it’s not the 5 most recent searches in the search table. Does that help you understand my situation?

11

what if you wrote the query for only one table, and included the ORDER BY clause, along with LIMIT 5

that would work, right?