Solved

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

Posted on 2009-07-16
11
445 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:egoselfaxis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24868719
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
 
LVL 14

Expert Comment

by:profya
ID: 24868733
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
 
LVL 14

Accepted Solution

by:
profya earned 500 total points
ID: 24868740
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:profya
ID: 24868746
@angelIII: It took 0 time to respond! :)
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 24869322
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
 

Author Comment

by:egoselfaxis
ID: 24871418
Thanks!

- yg
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871529
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
 

Author Comment

by:egoselfaxis
ID: 24871546
The dates are formatted as yyyy-mm-dd
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871600
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24871603
aka: prove me wrong with data samples...
0
 

Author Comment

by:egoselfaxis
ID: 24871789
You might be right .. but the problem I was having was actually resulting from some improperly formatted dates in the database.  

- yg
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question