Why are my dates not sorting correctly?

Question

I wonder if anyone has come across this issue before.
I have a string converted to a date and sorted ascending. The date is sorting numerically but it is not sorting on the month. I wonder if anyone has had this issue and can shed some insight as to how to get the date to sort correctly.

SELECT
  u.url_id, 
  url, 
  title, 
  description, 
  pub_date, 
  DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y'), '%d.%b.%Y') AS pub_date,
  pub_date AS sortdate 
FROM 
  urls AS u, 
  url_associations AS ua 
WHERE
  u.url_id = ua.url_id 
  AND ua.url_category_id=$type
  AND ua.approved = 'Y'
ORDER BY
  sortdate DESC

The above is the code and it works but the date isn't sorting eg it sorts like this:

 29-may-2009
 28-may-2009
 27-may-2009
 02-june-2009
 01-june-2009

Answer 1

Try

"SELECT u.url_id, url, title, description, pub_date, DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y'), '%d.%b.%Y') AS pub_date, pub_date AS sortdate FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AND ua.url_category_id=$type AND ua.approved = 'Y' ORDER BY pub_date DESC";

The problem is you have

 pub_date AS sort_date

inside the query, and so when you sort by sort_date you sort by the string. Replace the sort order by

ORDER BY pub_date DESC

and everything should work.

Answer 2

I have had similar issues. What I have done (and only using ORACLE) is use the date_format in your orderby instead of the already formatted date.

so in your order by use:

DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y') DESC

or - which will first order by year

DATE_FORMAT(STR_TO_DATE(pub_date, '%Y-%b-%d') DESC

Answer 3

It looks like "pub_date" is a string field? If so you would need to convert it to datetime for sorting to work as expected.

Answer 4

You are telling the query to sort on the converted string, which means that it is doing sorting on a string and not a date. Try ordering by pub_date instead.

Answer 5

Okay, I was a bit confused before. Your original query is somewhat confusing as you're selecting pub_date in the list of columns, and then a conversion also as pub_date. However, you were then sorting by the pub_date column (effectively, given sortdate as pub_date) - which appears to be a string column.

Your ordering should be on the column after conversion to a date, but before conversion to a string:

SELECT
  u.url_id, 
  url, 
  title, 
  description, 
  pub_date,
  STR_TO_DATE(pub_date, '%d-%b-%Y') AS sortdate,
  DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y'), '%d.%b.%Y') AS formatted_date
FROM 
  urls AS u, 
  url_associations AS ua 
WHERE
  u.url_id = ua.url_id 
  AND ua.url_category_id=$type
  AND ua.approved = 'Y'
ORDER BY
  sortdate DESC

Note that I've renamed the "formatted" version to formatted_date. It's not clear whether you still need to select the original pub_date or not. It's possible that the formatted_date bit could be:

DATE_FORMAT(sortdate, '%d.%b.%Y') AS formatted_date

but I'm not entirely sure. I'd hope that the query optimizer would figure that out anyway.

Does your pub_date column really have to be a string? Why not keep it as a more appropriate type in the database to start with, to avoid all the parsing?

Answer 6

You want to sort like this..

ORDER BY DATE_FORMAT(STR_TO_DATE(pub_date, '%d-%b-%Y'), '%d.%b.%Y')

But if I may, methinks you should consider changing base column from string to date - lots of reasons: scalability, performance, maintainability, data integrity, yada yada yada.