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 can CAST your varchar values to DATETIME or DATE type.

WHERE CAST(dates AS DATE) BETWEEN (7/28/10 and 7/29/10)

This might not be an optimal solution, because you may lose the benefit that indexes provide.

Answer 2

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'

Answer 3

IF you are using SQl use this query

Data

DECLARE @Dates TABLE (StartDate varchar(100));

INSERT INTO @Dates VALUES ('7/1/2010 9:10 AM');
INSERT INTO @Dates VALUES ('7/5/2010 10:33 AM');
INSERT INTO @Dates VALUES ('7/13/2010 04:53 AM');
INSERT INTO @Dates VALUES ('7/22/2010 8:45 AM');
INSERT INTO @Dates VALUES ('7/10/2010 11:20 AM');
INSERT INTO @Dates VALUES ('7/11/2010 12:40 AM');

Query:

SELECT * FROM @Dates
WHERE (CONVERT(datetime,StartDate,101) >= CONVERT(datetime,'7/1/2010 9:10 AM',101))
                                         AND (CONVERT(datetime,StartDate,101) <= CONVERT(datetime,'7/15/2010 9:10 AM',101))
ORDER BY CONVERT(datetime,StartDate,101)