CODEWITHSUNDEEP
mysqldatesql-order-by
Guido Visser
Guido Visser

Reputation: 2299

MYSQL Query: ORDER BY specific date

I am currently creating a website on which I can add videos. When I add a video, I need to give a release date, formatted like: "21-09-2012".

Now, when I use the simple ORDER BY command, it only sorts on the first two digits.

Does anyone know how can I fix this?

Upvotes: 0

Views: 290

Answers (2)

Daves
Daves

Reputation: 11

After very boring research, I had a brilliant idea:

Format: 18/11/2014

select id,
       date_string,
       substr(date_string, 1, 2) as day,
       substr(date_string, 4, 2) as month,
       substr(date_string, 7, 4) as year
from myTable
order by year, month, day

Optionally you can do the same logic to add hours, minutes, seconds...

Upvotes: 1

Taryn
Taryn

Reputation: 247680

It sounds like you are storing your date field as a string instead. If that it the case then you will need to convert the value to a date before trying to order it:

SELECT  STR_TO_DATE(yourdate, '%d-%m-%Y')
FROM    yourtable

MySQL documentation on STR_TO_DATE

However, you should be storing your dates in a date datatype and not as a string value. Then you will not have to perform these types of conversions.

You can also do the conversion in the ORDER BY statement itself:

order by STR_TO_DATE(yourdate, '%d-%m-%Y')

Upvotes: 4

Related Questions