?
Solved

Comparing dates in MYSQL is not working after MYSQL upgrade

Posted on 2008-06-26
3
Medium Priority
?
296 Views
Last Modified: 2010-04-21
I have a query that has run for multiple years, we recently upgraded (5.0.45).  It appears that comparing using the Date() function no longer is working correctly.

Attaching the query below, the ship_dt column is a timestamp column and we are only wanting to compare the date and not the entire timestamp.

As mentioned this worked perfectly fine under 4.x

Any help would be greatly appreciated.
SELECT DISTINCT a.orderid FROM orders a WHERE a.orderstatus=2 AND DATE(a.ship_dt)='2008-06-26'

Open in new window

0
Comment
Question by:pmessana
[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
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
CoyotesIT earned 375 total points
ID: 21876010
My suggestion would be to use the date_format inyour WHERE clause, since this is a timestamp you have the hh:mm:ss along with that value, you will need to strip that portion of the date/time off and just compare the '%Y-%m-%d' in your query.

try something like:

SELECT DISTING A.orderid FROM orders WHERE A.orderstatus = 2 AND DATE_FORMAT(a.ship_dt, '%Y-%m-%d')='2008-06-26'

Good luck!

~CoyotesIT

0
 
LVL 9

Author Closing Comment

by:pmessana
ID: 31470966
Although I wish that I could understand why this suddenly stopped working your solution did work.
0
 
LVL 8

Expert Comment

by:CoyotesIT
ID: 21886753
I'm glad it worked for you, MySQL 5 is just more standardized. What's with the grade?

CoyotesIT
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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

752 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