CODEWITHSUNDEEP
mysqldate
Margz
Margz

Reputation: 23

Event Date Asc Not Right

how to do this right

Select DATE_FORMAT(EventDate, '%b %d, %Y') 
from details 
Order by STR_TO_DATE(EventDate, '%d-%m-%y')

sample records,

Oct 24, 2012
Oct 27, 2012
Oct 28, 2012
Oct 20, 2012
Dec 22, 2012
Jan 11, 2013
Jan 19, 2013
Nov 24, 2012
Dec 29, 2012

Upvotes: 0

Views: 76

Answers (2)

Álvaro González
Álvaro González

Reputation: 146410

Order by STR_TO_DATE(EventDate, '%d-%m-%y')

So we start with EventDate which you say is a DATE and we feed it as first argument to STR_TO_DATE(). Such function expects a string, so MySQL casts your date as string possibly using the default format (YYYY-MM-DD). Then it tries to cast the string to date using DD-MM-YYYY as format but your string is not in that format. Do you understand where I want to go?

To sum up: you don't need to cast a date to date--it's already a date!

Order by EventDate

Upvotes: 0

Tom
Tom

Reputation: 6663

What about this:

Select DATE_FORMAT(EventDate, '%b %d, %Y') from details Order by EventDate

Upvotes: 1

Related Questions