DATETIME

[OAFC_Rob]

I have a news article table with a field set to "DATETIME", when I'm running the SQL i'm formatting the using SQL rather than PHP and then ordering by the date / time. However, I noticed yesterday that it was only sorting via the date and not the date and then the time.

 

 

Should I swap it to a date and timestamp, as in two seperate fields.

 

SELECT
         tbl_news_id, 
         headline,
         article,
         DATE_FORMAT(datePublished, '%D %M %Y @ %H:%i' ) AS datePublished, 
         rssFeed
FROM
         tbl_news
ORDER BY 
         datePublished 
ASC LIMIT 2

[Muddy_Funster]

I'm pretty confident that whatever you do during the select has no bearing on the field used in the group by....

[PFMaBiSmAd]

Use a DIFFERENT alias name for the DATE_FORMAT output so that mysql can order by your original column, not the DATE_FORMAT output.

[OAFC_Rob]

What and then order by DESC, which would put in the following order correct

 

UK Date Format

05.09.2011 @ 16.47

05.09.2011 @ 16.45

[PFMaBiSmAd]

DATE_FORMAT(datePublished, '%D %M %Y @ %H:%i' ) AS use_a_different_name_here,

 

ORDER BY datePublished <---- so that the ORDER BY will use your datePublished column values instead of the DATE_FORMAT() output.