searching data between dates stored in varchar in mysql

Question

I am storing my dates in column server_date_time in varchar in dd/mm/yyyy format and i want to fetch the records lying between some dates so i have used the query

    select * from activity_emp 
where date_format(str_to_date(substr(server_date_time,1,10),'%d/%m/%Y'),'%d/%m/%Y')>=
'29/09/2012'
    and date_format(str_to_date(substr(server_date_time,1,10),'%d/%m/%Y'),'%d/%m/%Y')<=
'07/10/2012';

I have converted varchar to string in query but my query return query data only related to 29/09/2012 and 30/09/2012. It should also return query for the month of October

Answer 1

Try with this. You can input date in dd/mm/yyyy format as in your question...

SELECT * FROM activity_emp
WHERE STR_TO_DATE(server_date_time, '%d/%m/%Y')
  BETWEEN STR_TO_DATE('29/08/2012', '%d/%m/%Y')
    AND STR_TO_DATE('07/10/2012', '%d/%m/%Y')

Update: I strongly recommend you to change datatype from VARCHAR to DATETIME

Cheers!!!

Answer 2

Try this one -

SELECT * FROM activity_emp
WHERE STR_TO_DATE(server_date_time, '%d/%m/%Y')
  BETWEEN '2012-09-29' AND '2012-09-30'

But it is better to store server_date_time in DATETIME data type so that MySQL can use index.