Help with dates on a query...

[Mr Chris]

Hi All,

 

I have a query which runs fine:

 

(SELECT news_id as id, published as sdate, headline, opening, 'c' AS tbl FROM news) UNION ALL

(SELECT report_id as id, match_date as sdate, headline, opening, 's' AS tbl FROM reports) ORDER BY sdate DESC LIMIT 1";

 

 

However, when I try and change the date query on it to output the date in DD/MM/YY format I get some strange results. Any ideas?

 

(SELECT news_id as id, DATE_FORMAT(published, '%d/%m/%y') as sdate, headline, opening, 'c' AS tbl FROM news) UNION ALL

(SELECT report_id as id, DATE_FORMAT(match_date, '%d/%m/%y') as sdate, headline, opening, 's' AS tbl FROM reports) ORDER BY sdate DESC LIMIT 1";

 

Thanks

 

[toplay]

You need to order by the "published" and "match_date" column and not sdate. You can still leave sdate to use for displaying, but need to still order by YYYY-MM-DD format. Example:

 

(SELECT news_id as id, published AS order_by_date, DATE_FORMAT(published, '%d/%m/%y') as sdate, headline, opening, 'c' AS tbl FROM news) UNION ALL

 

(SELECT report_id as id, match_date  AS order_by_date, DATE_FORMAT(match_date, '%d/%m/%y') as sdate, headline, opening, 's' AS tbl FROM reports) ORDER BY order_by_date DESC LIMIT 1";