[RESOLVED] Ordering by DATE_FORMAT() gives odd results

TobyRT

Hi guys,

I'm not sure why, by I've just noticed this oddity in some of my code. It's been working fine for a while, but for some reason it's now gone all Pete Tong on me.

This is what used to work. With a limiter, it would bring up the correct two forthcoming titles for the bookclub meetings. I noticed that this was causing problems when it was still listing the meeting for September 4th, then skipping October and moving to November.

SELECT
b.id,
b.title,
b.author,
b.imgaddress,
d.bcid,
DATE_FORMAT(d.date, "%d %M %Y") as date
FROM bookclub2 b, event_details d
where b.id=d.bcid
ORDER BY date ASC

I ran the query directly on my database, and realised that is was definitely the sort that wasn't working properly.

I changed my query to incorporate the unformatted date (in addition to formatted), and sorted on that instead.

Lo, it worked - but I'm buggered if I know why - could you shed any light on this?

I've got it working - I'd just like to know why this is, so I can avoid it in the future!

tomhath

It's sorting a formatted string, not a date. So if the days are different it'll do what you told it to do, e.g:

"04 September 2006"
"06 November 2006"
"08 October 2006"

mjax

Try this:

SELECT
b.id,
b.title,
b.author,
b.imgaddress,
d.bcid,
DATE_FORMAT(d.date, "%d %M %Y") as date_text
FROM bookclub2 b, event_details d
where b.id=d.bcid
ORDER BY date ASC

TobyRT

Thanks Tom - I thought that's what it might be - I just can't quite believe I've been so lucky that all my dates were sequential up until now! (and interestingly, the dates you picked were identical bar one to the ones I need - are you stalking me? 😛)

Likewise ajax - didn't realise i could sort on a field I hadn't included in my select (although I shoulda guessed really.

Ta muchly chaps