How to get Records between two dates in MySQL / PHP when Date Field is of VARCHAR type

How to compare two dates in MySQL

 Some times, we need to compare two dates in a  mysql table but unfortunately date field is VARCHAR type. and you can't use sql commands like following

Select * from table where `fieldone` is between 'date1' and 'date2'

Remember, there is one basic THUMB rule when comparing two dates in mysql table. MySQL only compares Dates in Y-m-d format.
You can't compare dates in any other format than like '2000-03-29'.

So if you want to compare dates in any other format, you first need to convert dates to mysql date format and then compare them. Suppose dates are saved in DB like 19/01/2003 , we can write sql query which can convert d/m/Y format into mysql date format we can use mysql function str_to_date

str_to_date('19/01/2003,'%d/%m/%Y)
// this will convert date to 2003-01-19

so  you can make a query
select * from table where str_to_date('coulumn','%d/%m/%Y) between '2001-01-19' and '2012-04-01'

and this query will return all records between Jan 19,2001 to April 01, 2012 when mysql field type is "Varchar"

Remember the thumb rule : mysql only compares dates in "Y-m-d" format so before comparing dates, first convert the dates to mysql data format

Convert Date format to MySQL Date format in PHP

You can use date() function to convert date from one format to other especially in mysql date format.  here is an example,

$mysql_date = date('Y-m-d', strtotime($original_date));