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));
