Brent Baisley wrote:
Don't look at it as a string, you're not searching on a string. What
you are actually searching on is a range of dates, the first of the
month through the end of the month. That will keep your data in a date
format and use the index.
SELECT ... WHERE date between CONCAT(YEAR(date),"-",MONTH(date),"-1")
AND LAST_DAY(date)
That will match all rows (every date is in its own month). Perhaps you meant
something like
SET @targetdate = '2005-06-15';
SELECT ...
WHERE date BETWEEN CONCAT(YEAR(@targetdate),"-",MONTH(@targetdate),"-01")
AND LAST_DAY(@targetdate);
I'd also suggest that your CONCAT(YEAR...MONTH...) is duplicating
functionality already provided by DATE_FORMAT():
SET @targetdate = '2005-06-15';
SELECT ...
WHERE date BETWEEN DATE_FORMAT(@targetdate,'%Y-%m-01')
AND LAST_DAY(@targetdate);
Of course, if you're doing this in an application, skip the SET statement and
just put the target date variable in place of @targetdate in the SELECT.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]