MySQL Bugs: #3366: Select Within Date Range Using Date Substrings

Bug #3366	Select Within Date Range Using Date Substrings
Submitted:	2 Apr 2004 6:09	Modified:	2 Apr 2004 6:55
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Windows (Windows Server 2003)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
There is a problem choosing a date range between particular dates when dates are specified as substrings.  The last date of the date range is incorrectly excluded and not returned.

You can use DATE_ADD as a workaround, but this is a basic functionality issue and should be fixed quickly.

How to repeat:
SELECT * 
FROM xxxx
WHERE
 b.Quote_Date >= '2004-3-1' AND b.Quote_Date <= '2004-3-31'

returns only rows containing 3/1/2004 through 3/30/2004

I used the following workaround:

SELECT * 
FROM xxxx
WHERE
 b.Quote_Date >= '2004-3-1' AND b.Quote_Date <= DATE_ADD('2004-3-1', interval 1 month)

returns all rows containing 3/1/2004 through 3/31/2004

I could not repeat this with preliminary testing.  Please submit a complete test case including the SQL to create, populate and query a table which demonstrates this behavior, so we can ensure that we are testing precisely the same thing.

Thank you.

I had the same problem

When I execute this query;

SELECT count(id) as referralspassed FROM feedlog_redirect WHERE addDate >= '2009-05-01' AND addDate <= '2009-07-31' AND custid = '333' 

It returns the records which are between 01-05-2009 and 30-07-2009

Please reply as soon as possible

Thanks 

Deniz