• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Comparing dates in MYSQL is not working after MYSQL upgrade

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
pmessana
Asked:
pmessana
  • 2
1 Solution
 
CoyotesITCommented:
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
 
pmessanaAuthor Commented:
Although I wish that I could understand why this suddenly stopped working your solution did work.
0
 
CoyotesITCommented:
I'm glad it worked for you, MySQL 5 is just more standardized. What's with the grade?

CoyotesIT
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now