mysql: searching BETWEEN dates stored as varchar

Question

i would like to select * from table where dates between (some_date and another_date)

the problem is that the dates are stored as varchar!

here are examples of dates that i have:

7/29/2010 9:53 AM
7/16/2010 7:57:39 AM

please notice that some records have seconds and some do not

i dont care about the time at all i just need the date

reporttime is the date field

this is not working:

SELECT * FROM batchinfo 
 where cast(reporttime as date) between ('7/28/10' and '7/29/10')

this:

SELECT * from batchinfo WHERE reporttime BETWEEN STR_TO_DATE(7/28/2010, '%m/%/d/%Y %h:%i:%s %p')
                AND STR_TO_DATE(7/29/2010, '%m/%/d/%Y %h:%i:%s %p')

is returning:

Truncated incorrect datetime value: '7/8/2010 11:47 AM'
Incorrect datetime value: '0.00012009' for function str_to_date

this:

SELECT * from batchinfo WHERE STR_TO_DATE(reporttime, '%m/%/d/%Y %h:%i:%s %p') BETWEEN STR_TO_DATE(7/28/2010, '%m/%/d/%Y')
                                                           AND STR_TO_DATE(7/29/2010, '%m/%/d/%Y')

is returning:

Incorrect datetime value: '7/8/2010 11:47 AM' for function str_to_date

OMG PONIES:

i am taking everything before the first blank:

SELECT * from batchinfo WHERE STR_TO_DATE(LEFT(reporttime,LOCATE(' ',reporttime)), '%m/%/d/%Y') BETWEEN STR_TO_DATE(7/28/2010, '%m/%/d/%Y')
                                                           AND STR_TO_DATE(7/29/2010, '%m/%/d/%Y')

and now i get this returned:

Incorrect datetime value: '7/8/2010' for function str_to_date

Answer 1

You want to search between dates, store them as dates. By storing them as strings you're shooting yourself in the foot. You'd basically need to extract date part from the string (using SUBSTR() or LEFT() ) and parse it to date format (using STR_TO_DATE()).

The performance of such solution will be appaling.

STR_TO_DATE(LEFT(reporttime,LOCATE(' ',reporttime)),'%m/%d/%Y') BETWEEN '2010-07-28' AND '2010-07-29'