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
Solved

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

Posted on 2009-07-16
11
444 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 14

Expert Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sum particular field in database 2 16
selector:validator cookies 4 30
Where on a calculated field 1 21
Requesting help with creating an SQL query with 2 tables 6 25
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
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…

856 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