sql - 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

Use STR_TO_DATE to convert the strings to the DateTime data type. The format shorthand is found under DATE_FORMAT:

STR_TO_DATE(column, '%m/%/d/%Y %h:%i:%s %p')

The problem is, you'll have to update the VARCHAR dates to all be the same format first, before you can use:

WHERE STR_TO_DATE(reporttime, '%m/%/d/%Y %h:%i:%s %p') BETWEEN STR_TO_DATE(some_date, '%m/%/d/%Y')
                                                           AND STR_TO_DATE(another_date, '%m/%/d/%Y')

Date formats are not consistent (some use hyphens, others slashes and Year/Month/day order can be totally different...), so STR_TO_DATE is the most accommodating & consistent means of turning a string into a DateTime. Only after the value is DateTime, does Date/Time functionality become available like DATE() to get only the date portion...

Because of the data type change, an index on some_date & another_date columns can not be used.