CODEWITHSUNDEEP
mysql
Code Spy
Code Spy

Reputation: 9954

Sort Date in Mysql table in DESC order

I want to show date column in DESC order where date is entered as VARCHAR and is in order 20-JUN-2007 I have already used ORDER BY RIGHT(vPublishedDate, 4) but it doesn't effect the month and date

Upvotes: 0

Views: 1433

Answers (6)

Pramod Solanky
Pramod Solanky

Reputation: 1690

I would suggest you to change the type as Date. Then run a script which converts your dates to the correct DB format.

Sorting would be then be just as simple as sorting ids in MySql

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881313

You really should be storing dates as dates, not character-type fields. Then you wouldn't need to worry about this sort of "SQL gymnastics" (as I like to call it).

Databases are for storing data, not formatting.

By forcing yourself to manipulate sub-columns, you basically prevent the database from performing any useful optimisations.

In order to do what you want with the data you have you have to do something like:

  • use substring to extract individual sub-column information to get them in the order you want; and
  • use some sort of lookup to turn a string like "NOV" into 11 (since the month names will sort as DEC, FEB, AUG, APR, JAN, JUL, JUN, MAR, MAY, NOV, OCT, SEP).

And this would be a serious performance killer. Now there may be a function which can turn that particular date format into a proper date but I urge you: don't use it.

Set up or change your database to use an intelligent schema and all these problems will magically disappear.

It's a lot easier to turn a date column into any sort of output format than to do the same with a character column.

Upvotes: 1

mathematical.coffee
mathematical.coffee

Reputation: 56905

Here is one way to do it using STR_TO_DATE (take into account the other answers about converting the column to date, although you may not have control over the database):

SELECT ...
FROM ...
ORDER BY STR_TO_DATE(vPublishedDate,'%d-%M-%Y')

As an example:

SELECT STR_TO_DATE('20-JUN-2007','%d-%M-%Y') as Date;
+------------+
| Date       |
+------------+
| 2007-06-20 |
+------------+

Upvotes: 2

Raghuram
Raghuram

Reputation: 3967

Try converting the varchar to date using str_to_date and then you can apply the sorting logic.

Upvotes: 0

Rishav Rastogi
Rishav Rastogi

Reputation: 15492

Change that VARCHARto a Date type column, if you can.

You can also try this, although this is NOT the RIGHT approach.

Select STR_TO_DATE(your_date_column,'%d/%m/%Y') AS your_new_date from your_table order by your_new_date DESC

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

Why are you using a VARCHAR to store a DATE? Use a DATE to store a DATE and then, as if by magic, sorting works all on its own.

Upvotes: 2

Related Questions