Mysql Query Question - ordering by date when date is stored in VARCHAR column

I'm trying to update a MySQL query so that it displays the records in reverse chronological order based on the "entry_date" value -- but the dates are stored in a VARCHAR column, which is making the records display in the wrong order.

How can I update the following MySQL query so that it displays the records in reverse chronological order based on the date in this situation?

SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY entry_date DESC

Thanks,
- Yvan
egoselfaxisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the format of the date value in the varchar column?
why is that a varchar field, anyhow, and not a date field!

the varchar format would need to be yyyy-mm-dd to be sorted correctly.
if it's any other format, use the function str_to_date() to get the varchar into a date value for the order by
ORDER BY str_to_date(entry_date, 'format goes here') DESC

Open in new window

0
profyaCommented:
SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY CATS(entry_date AS Date) DESC
0
profyaCommented:
SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY CAST(entry_date AS Date) DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

profyaCommented:
@angelIII: It took 0 time to respond! :)
0
gr8gonzoConsultantCommented:
Just a note on performance - using "ORDER BY" in MySQL in combination with some function is USUALLY not a very optimized query. If the query is only running on a few rows (5, 10, 20, etc), then it's probably not a big deal and you won't notice the difference. Try it on a result with 10,000 rows, and you'll see a noticeable difference. Try it on a result with 500,000 rows, and you'll immediately be looking for a different way to do this.

So if you expect this table and result set to be large at any given time, I'd recommend just fixing the problem now. I personally convert dates like this to UNIX timestamp and store them in an INT field. At that point, you can run a normal ORDER BY on that field without any special functions or parameters, and it'll sort all the way down to the second.

No matter what path you choose, though, try to avoid having large result sets at all costs for maximum performance on your database. (Using a tool like MySQLTuner can also help you determine better ways of optimizing your database settings.)
0
egoselfaxisAuthor Commented:
Thanks!

- yg
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
huh? I am surprised

because CAST(entry_date AS Date)  shall only work if entry_date is formatted yyyy-mm-dd, in which case the order by should work anyhow.
please clarify
0
egoselfaxisAuthor Commented:
The dates are formatted as yyyy-mm-dd
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, I take this from my first comment:
>the varchar format would need to be yyyy-mm-dd to be sorted correctly.

as this is the case, your ORDER BY should work correctly already without the CAST() ...
in other words, you original query is ok as is:

SELECT ID,title,location,entry_date, article_text FROM newsevents WHERE entry_type!='EVENT' AND archive='0' AND publish_main='1' ORDER BY entry_date DESC

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
aka: prove me wrong with data samples...
0
egoselfaxisAuthor Commented:
You might be right .. but the problem I was having was actually resulting from some improperly formatted dates in the database.  

- yg
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.