Solved

Comparing dates in MYSQL is not working after MYSQL upgrade

Posted on 2008-06-26
3
286 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
  • 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

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

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