Can we user str_to_date in where clause

[saadi06]

Hi,
I am having a problem in making a query. I have a table in which dates are saved in
varchar format and I want to compare these dates in my search query. I want to take two input dates from the user i.e. starting date and ending date and I want to compare these dates with my varchar fields in which dates are saved. As far as I know we cannot compare dates if they are in varchar format. I made a query for this

SELECT id,start_date,end_date FROM 
dates WHERE 
(start_date,'%m/%d/%Y')>='2012-01-01' AND (end_date,'%m/%d/%Y')<='2012-12-01' 

I have values in my table which fall in these dates but it is not displaying me any result. The dates saved in my table are in the format of m/d/Y and I have checked the query if I execute this query

  SELECT id,STR_TO_DATE (start_date,'%m/%d/%Y'),STR_TO_DATE (end_date,'%m/%d/%Y')  FROM 
dates 

It prints the result of the selected date in the date format in which mysql stores the date. Please Help. Thanks in advance

can't you change fields to date or integer types (Y-m-d or unix timestamp)? storing data like this may be problematic. You could run an update on the fields to convert them to the right format and then change the datatype.

[Biiim]

Ardav is right, mysql won't be able to make proper use of indexes either

ALTER TABLE `dates` ADD COLUMN `start_date2` DATE NULL AFTER `start_date`,     ADD COLUMN `end_date2` DATE NULL AFTER `end_date`;
UPDATE `dates` SET start_date2 = STR_TO_DATE(start_date,'%m/%d/%Y'), end_date2 = STR_TO_DATE(end_date,'%m/%d/%Y');

If its not possible to change it your query would be:

SELECT id,start_date,end_date 
FROM dates 
WHERE STR_TO_DATE(start_date,'%m/%d/%Y') >='2012-01-01' AND STR_TO_DATE(end_date,'%m/%d/%Y') <='2012-12-01';

STR_TO_DATE (

[saadi06]

No I cannot change the fields as I have to change it in the whole project and there is too much data in the table

The amount of data in the table doesn't matter as the update query will deal with it ALL. I assume that you'd only have to change the DB input and output code. The time taken to make these changes should be worth it. Not taking these steps could give you a prize headache and I assume duplicating date handling in your SQL and php.