CODEWITHSUNDEEP
mysqlsql
0x49D1
0x49D1

Reputation: 8704

DATE type comparison in MySQL

Had a bit unintuitive case right now with MySQL:
the query contains where clause with comparison: WHERE t.date = '2016-12-31' (t.date-s datatype is DATE(!)).. And it returns no records on execution. But the query: WHERE t.date > '2016-12-31' - returns the records with date equals '2016-12-31' among other records! The record for 2016-12-31 also showed up in case I've used BETWEEN '20161231' AND '20170101'. Tried formattings, type changes - nothing helped. After some time spent on searching for cause I did the following: updated the record's date column manually, SETting it to '2016-12-31'. After this action WHERE t.date = '2016-12-31' started to work as expected.
Probably I'm missing something, wondering what can cause such behavior.
Update
date is DATE, not DATETIME

After doing manual update I can't reproduce the mentioned behavior again: now any type of comparison(=, DATE(..)=, STRCMP) - works as it should!
Update 2
For 2016-11-30 and 2016-09-30(end of months!) found the same behavior! Won't update the record manually for now to test the suggestions I get here.
Update 3
I've also run OPTIMIZE TABLE on the table with that date column to rebuild indexes for elimination any problems with corruption.
Update 4
Here is more:
if I check HEX values for the date field for incorrect fields(end of month) I get wrong values!
SELECT HEX(t.date) FROM table t WHERE t.date BETWEEN DATE('20160930') AND DATE('20161001');
Returns:
323031362D31302D3030
323031362D31302D3031
SELECT HEX(DATE('20160930'));
Returns:
323031362D30392D3330
And 323031362D30392D3330 != 323031362D31302D3030
SELECT X'323031362D31302D3030';
And it returns:
2016-10-00, NOT 2016-09-30!
For the value that I've updated manually - HEX is same.
But what can cause such difference?

Upvotes: 1

Views: 212

Answers (2)

0x49D1
0x49D1

Reputation: 8704

After some investigation I've found the problem and its not related directly to the date comparison in MySQL. I'll post it here in case anyone is stuck at such case.
I've found that the problem was with selecting results in IDE (in my case DataGrip): the value for date field in database was 2016-10-00 and select was returning 2016-09-30! That was confusing.. But after the 00 DAY was found - it was relatively easy to find the cause of it: CURDATE() - 1 (in my case there should have been: CURDATE() - INTERVAL 1 DAY). Don't ever use date related functionality without specific functions like INTERVAL!!
Thanks to everyone who supported the question, sorry for confusion, I was confused too and found the answer only after several steps.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

Try forcing the format using 

WHERE date(t.date)  = '2016-12-31'

or 

WHERE date(t.date)  = str_to_date( '2016-12-31', '%Y-%m-%d')

or based on your test 

WHERE date(t.date)  = str_to_date( '20161231', '%Y%m%d')

Upvotes: 1

Related Questions