[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-07-16
11
Medium Priority
?
448 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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