Solved

Comparing dates in MYSQL is not working after MYSQL upgrade

Posted on 2008-06-26
3
288 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 125 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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