Solved

Comparing dates in MYSQL is not working after MYSQL upgrade

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use Check Constraints in MySQL 2 36
Clean text to insert in database 9 51
No row return after calling the fillschema method 4 39
MySQL Grouping 2 22
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now