Help with Incorrect date values...

[mellis95]

I have run into an issue that I have not been able to find any information about by searching either Google or these forums. I have been running this query for over a year now, with no problem. It simply counts records between two dates. Today, while refactoring the code on the same server, but using a fresh (non-production) database (in PHP), I noticed that none of my counts against this particular table were working correctly anymore. The thing is, Running my original query via PHP yields correct results. Running my original or query from a command line, I get the following:

 

mysql> SELECT COUNT(referral_id) FROM tbl_referral_info WHERE pt = 1 AND refDate BETWEEN 2010-01-11 and 2011-01-11;
+--------------------+
| COUNT(referral_id) |
+--------------------+
|                  0 |
+--------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '1998' for column 'refDate' at row 1 |
| Warning | 1292 | Incorrect date value: '1999' for column 'refDate' at row 1 |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

 

All of the information that I have found through Google and these forums, only references Inserts and Updates not working due to incorrect date values. How is it that a Select is not working?  I have attempted to find the records with the incorrect date value, but can find nothing out of the ordinary. What am I missing here?

 

EDIT: (forgot to give additional info)

 

`refDate` date DEFAULT NULL

`pt` tinyint(1) DEFAULT '0'

 

mysql  Ver 14.14 Distrib 5.1.37

 

 

 

Thanks in advance,

Matt

 

[jdavidbakr]

Put your dates in quotes:

 

mysql> SELECT COUNT(referral_id) FROM tbl_referral_info WHERE pt = 1 AND refDate BETWEEN '2010-01-11' and '2011-01-11';

 

Otherwise it will evaluate 2010 minus 1 minus 11 which is why it's giving 1998/1999 as the non-dates.

[mellis95]

WOW. That does it. It is always something obvious that I just overlook. The bad part is, my production code has quotes around it, and even comparing the production code with what I was working on for refactoring, I still missed it!

 

Thanks for the help.

 

Matt